Concatenate with Delimiter and Some Blank Cells

cpaterso · December 23, 2021, 9:33pm

I’ve searched around to see if I can find out to do this easily but to no avail.

I have 20 columns. Image 1, Image 2, Image 3, up to Image 20. I need to combine these columns into 1 column, separated by a delimiter.

The problem is, not every cell in every row has a value. For example, I may only have 5 images for one row. On another row, Image 1, Image 2, and Image 4 may have content, but we have deleted Image 3 in the past so it is now blank.

With standard concatenate, you end up with [Image 1], [Image 2], - a bunch of commas.

I suppose what I’m trying to do is combine these with a delimiter, but ignore all blank columns.

Other than writing a really long if statement to check if each one is empty for all fields, does anyone have any ideas? Thanks!

cpaterso · December 23, 2021, 9:57pm

I would still like some guidance to see if there is an easier way, but what I did:

Run replace on all columns to check if Empty. If empty, say EMPTY, otherwise use original value.

if(isempty([Image 2])=TRUE(),“EMPTY”,[Image 2])
if(isempty([Image 3])=TRUE(),“EMPTY”,[Image 3])

And so on.

Then run Concatenate function for all columns, with comma:

[Image 2]&","&[Image 3]&","&[Image 4]&","&[Image 5]&","&[Image 6]&","&[Image 7]&","&[Image 8]&","&[Image 9]&","&[Image 10]&","&[Image 11]&","&[Image 12]&","&[Image 13]&","&[Image 14]&","&[Image 15]&","&[Image 16]&","&[Image 17]&","&[Image 18]&","&[Image 19]&","&[Image 20]

Then replace “,EMPTY” with blank “”.

Then clean up last remaining EMPTY (if first one was empty) by replacing “EMPTY” with blanks.

Still a lot of work…

dgudkov · December 23, 2021, 10:08pm

Hi Cameron and welcome to the Community!

You can unpivot the table with the “Unpivot” action, then remove empty cells, then use “Aggregate” in the “Concatenate” mode to concatenate non-empty values. To preserve rows, enumerate before unpivoting.

See the example below.
concatenate-columns.morph (3.3 KB)