Ignore blank cells in concatenation
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 + "|"
Best Answers
-
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!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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...
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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 + "|", "")))
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
-
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!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå this is working as expected. Thanks a lot
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Good catch! I had tested JOIN for this use case but couldn't figure out how to get it to ignore the blank cells. Now I know!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks! I love the picture. ;)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!