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!
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 : )
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. 😞
Thank you so much!! 🙂
Happy to help : ) @insomned
If the response helps please don't forget to mark it as solution.
Cheers and have a nice day!
Have an amazing day yourself!
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).