Ignore blank cells in concatenation

Joseph Aloysias · 02/20/23

I want to concatenate multiple columns data in a cell but would like to add a condition to ignore blank cells,

I used a "|" to identify two different data's but if there is any blank cells "|" this symbol is keep adding without any data in between,

=Product@row + " " + Variant@row + "|" + [Product 0]@row + " " + [Variant 0]@row + "|" + [Product 1]@row + " " + [Variant 1]@row + "|" + [Product 2]@row + " " + [Variant 2]@row + "|" + [Product 3]@row + " " + [Variant 3]@row + "|" + [Product 4]@row + " " + [Variant 4]@row + "|" + [Product 5]@row + " " + [Variant 5]@row + "|" + [Product 6]@row + " " + [Variant 6]@row + "|" + [Product 7]@row + " " + [Variant 7]@row + "|"

Andrée Starå · 02/21/23

https://community.smartsheet.com/discussion/comment/363324#Comment_363324

Hi Dominic,

I hope you're well and safe!

Try something like this. (update the column names to match yours)

=JOIN(COLLECT([Product 1]@row:[Variant 6]@row, [Product 1]@row:[Variant 6]@row, <>""), " | ")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå · 02/21/23

@Domnic Victor

Excellent!

Happy to help!

Jeff Reisman · 02/20/23

@Domnic Victor

You're going to have to check if each cell contains text as part of the formula. Just because of how long this formula could become, I would suggest creating some helper columns to combine each set of Product and Variant values, and then use you formula to combine the values in those helper columns?

ProdVar column =Product@row + " " + Variant@row

ProdVar0 column = [Product0]@row + " " + [Variant0]@row

etc.

You'll cut your other formula size in half that way.

Here's how to avoid the blanks and extra post symbols:

=IF(ISTEXT(ProdVar@row), ProdVar@row + "|", "") + IF(ISTEXT([ProdVar0]@row), [ProdVar0]@row + "|", "") + IF(ISTEXT([ProdVar1]@row, [ProdVar1]@row + "|", "") + IF(ISTEXT(... keep going from here

In English: IF the value in ProdVar column is text (i.e. not blank,) give me the value from ProdVar plus a "|"; otherwise, leave it blank. Add the next IF for the ProdVar0 column, and the next...

Joseph Aloysias · 02/20/23

Hi @Jeff Reisman Thank you for the formula, I tried to make some correction in the formula as per my requirement

I'm getting the right value but its adding a "0" in front if there is no value in the first column,

=(IF(ISTEXT(Variant@row), Product@row + ": " + IF(ISTEXT(Product@row), Variant@row + "|", "")) +

IF(ISTEXT([Variant 0]@row), [Product 0]@row + ": " + IF(ISTEXT([Product 0]@row), [Variant 0]@row + "|", "")) +

IF(ISTEXT([Variant 1]@row), [Product 1]@row + ": " + IF(ISTEXT([Product 1]@row), [Variant 1]@row + "|", "")) +

IF(ISTEXT([Variant 2]@row), [Product 2]@row + ": " + IF(ISTEXT([Product 2]@row), [Variant 2]@row + "|", "")) +

IF(ISTEXT([Variant 3]@row), [Product 3]@row + ": " + IF(ISTEXT([Product 3]@row), [Variant 3]@row + "|", "")) +

IF(ISTEXT([Variant 4]@row), [Product 4]@row + ": " + IF(ISTEXT([Product 4]@row), [Variant 4]@row + "|", "")) +

IF(ISTEXT([Variant 5]@row), [Product 5]@row + ": " + IF(ISTEXT([Product 5]@row), [Variant 5]@row + "|", "")) +

IF(ISTEXT([Variant 6]@row), [Product 6]@row + ": " + IF(ISTEXT([Product 6]@row), [Variant 6]@row + "|", "")) +

IF(ISTEXT([Variant 7]@row), [Product 7]@row + ": " + IF(ISTEXT([Product 7]@row), [Variant 7]@row + "|", "")))

Jeff Reisman · 02/20/23

@Domnic Victor

You're getting the 0 because of the way you're using ISTEXT. The result of something like =ISTEXT(ColumnValue@row) is going to either be 1 or 0, true or false - because it's a boolean test.

The logic in my formula says "check if the cell value is text, and if it is, give me that value - otherwise give me no value at all.

I can't see what is happening in your logic to treat that first column's check as the value - but it might be the extra parentheses immediately following IF:

You logic is kind of strange too, but I don't really understand your use case.

Joseph Aloysias · 02/21/23

my use case is to concatenate values of product 1 to 6 and variant 1 to 6 ignoring blank cells

all rows are created by filling the form, so I can be able to add one product & variant at a time, here the problem is there are so many empty cells in the sheet I want to hide all of them and show values in consolidated column

Andrée Starå · 02/21/23

https://community.smartsheet.com/discussion/comment/363324#Comment_363324

Hi Dominic,

I hope you're well and safe!

Try something like this. (update the column names to match yours)

=JOIN(COLLECT([Product 1]@row:[Variant 6]@row, [Product 1]@row:[Variant 6]@row, <>""), " | ")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!