Complex IF Formula Required

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!

Datasets

Developer

Accepted answers

All comments

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 : )

concat.yxmd

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. 😞

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).

insomned.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703