Complex IF Formula Required

[insomned]

I want to concatenate cells from multiple columns in my dataset, separated by a semicolon. Some of the columns however, have [Null] values in them and so when I concatenate, I just get another semicolon (and this shouldn't be since each of the data points can only be separated by ONE semicolon). 

I guess I have to write an IF formula for IF the particular cell (under column) is empty (ie [Null]) then do not include it. There are multiple columns too.

Help would be appreciated! 

[atcodedog05]

Hi @insomned 

First I am concating then I am removing all the extra semicolon using regex and trim. This way I don't need to use any complex IFs 😅

Workflow:

Hope this helps : )

[insomned]

Looks good, but doesn't really work on my data since I have 8 columns like that and sometimes only 2 or 3 of them have data. 😞

[atcodedog05]

Hi @insomned 

Please find the updated workflow. This should handle 🙂

Hope this helps : )

[insomned]

Thank you so much!! 🙂 

[atcodedog05]

Happy to help : ) @insomned 

If the response helps please don't forget to mark it as solution.

Cheers and have a nice day!

[insomned]

Have an amazing day yourself!

[jrgo]

@insomned 

Here's an alternate approach if looking for a dynamic approach. RecordID tool is used for grouping, but can use your own if your data already has a unique row identifier.

Data is transposed on the group and then use a summarize tool to concatenate. The concat function automatically ignores NULL fields. Results are joined back on the record id.

The dynamic part is in the Transpose tool where you can set it to automatically include new fields found (Dynamic or Unknown Columns).