How to Concatenate Strings with nested IF statements

Hi,

I have four line items(Text1, Number1, Text2, Number2) I am trying to concatenate if those values are not blank or not equals to zero. See below formula. But I am getting formula invalid error. Please suggest how can i do concatenation of multiple string with nested IF statements.

Note: Please suggest me any Idea with only nested IF's not by creating system module or any other modules because i have a size limitation.

IF ISNOTBLANK(Text1) THEN Text1 ELSE

IF Number1>0 THEN & " " & TEXT(Number1) ELSE

IF ISNOTBLANK(Text2) THEN & " " & Text2 ELSE

IF Number2>0 THEN & " " & TEXT(Number2) ELSE BLANK

Thanks,

Suman.

Accepted answers

jasonblinn

Got it. In that case, I would try something lIke this:

Text1

& (IF 'Number1' > 0 THEN " " & TEXT('Number1') ELSE BLANK)

& (IF ISNOTBLANK('Text2') THEN " " & 'Text2' ELSE BLANK)

& (IF 'Number2' > 2 THEN " " & TEXT('Number2') ELSE BLANK)

This way, it is basically independent If Statements that get concatenated and calculate Blank if they are not met. Ideally, this could be staged out and concatenated in different lines.

If the space is avoidable in between the text bits I would encourage that as it would cut down drastically on the number of concatenations you are doing. You could even remove the 2nd If statement in that case and the formula would read:

Text1

& (IF 'Number1' > 0 THEN TEXT('Number1') ELSE BLANK)

& Text2'

& (IF 'Number2' > 2 THEN TEXT('Number2') ELSE BLANK)

Hope this helps,

Jason

All comments

AnjuR

Hi @Suman Reddy ,

I would suggest, as a best practice, to limit the # of concatenations you're doing as a whole by centralizing all of the potential concatenations in a system module. This way your if statement is just referring to concatenations that have been calculated once as opposed to every single time the IF statement is being processed.

You could also use booleans to check if the fields are blank and use that in your statement as well to optimize it further.

Suman Reddy

@AnjuR Thanks for your suggestion.

But due to size limitation i want to achieve only with nested IF's. Suggest me if you have any other idea how to do with nested IF instead of system module or some other module creation.

Best Regards,

Suman.

DaanishSoomar

Hey @Suman Reddy,

Doing text concatenations with this intensity, is certainly against best practice but I may have a solution for you. Before diving into it, just wanted to ask if your team has access to some kind of ETL tool where perhaps as part of the data load, you could do this concatenation before the data arrives to Anaplan?

Here is the solution:

IF ISNOTBLANK(Text4) THEN Text 1 & " " & Text 2 & " " & Text 3 & " " & Text 4 ELSE

IF ISNOTBLANK(Text3) THEN Text 1& " " & Text 2 & " " & Text3 ELSE

IF ISNOTBLANK(Text2) THEN Text 1 & " " & Text2 ELSE

IF ISNOTBLANK(Text1) THEN Text1 ELSE BLANK

Reasoning, I believe by reversing the order of the concatenations, this will run through all possible scenarios. Whereas in your original formula, if TEXT1 was not blank then the formula would take Text 1 and be done (I believe).

If anyone has a counter reason to flip the order back to original, happy to hear thoughts on that too. This is just my first glance gut reaction.

Also converting the IF ISNOTBLANK logic and storing them in Booleans would likely help the calculation perform better. But if space is a concern then the above would be the most concise way to do it. Though it would be slightly worse performance, in favor of saving space.

jasonblinn

@Suman Reddy

Curious if you need IF statements at all... Wouldnt it work to just do Text1 & Text2 & Text3 & Text 4? If any of them are blank, they will not have a value and will not combine anything. The IF Statements seem unnecessary.

Jason

DaanishSoomar

That is great insight! And an even better and simpler solution! @jasonblinn

Suman Reddy

@jasonblinn Very good observation on my requirement.

It was my mistake to give all line items as text line items in my example . I have numeric line items as well to concatenate after changing them into text. It means my conditions are based on numeric value >0. I hope this clears the confusion on my requirement.

Best Regards,

Suman.

Suman Reddy

@DaanishSoomar Unfortunately i do not have ETL tool.

Thank you for this suggestion. Seems like it works for me.

I will wait for few more ideas from others, Let us see if any innovative ideas come here.

Best Regards,

Suman.

jasonblinn

Got it. In that case, I would try something lIke this:

Text1

& (IF 'Number1' > 0 THEN " " & TEXT('Number1') ELSE BLANK)

& (IF ISNOTBLANK('Text2') THEN " " & 'Text2' ELSE BLANK)

& (IF 'Number2' > 2 THEN " " & TEXT('Number2') ELSE BLANK)

This way, it is basically independent If Statements that get concatenated and calculate Blank if they are not met. Ideally, this could be staged out and concatenated in different lines.

Text1

& (IF 'Number1' > 0 THEN TEXT('Number1') ELSE BLANK)

& Text2'

& (IF 'Number2' > 2 THEN TEXT('Number2') ELSE BLANK)

Hope this helps,

Jason

Suman Reddy

@jasonblinn I believe it is the perfect solution. I am going to implement it.

Thank you very much.

Suman

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…