Ignore blank cells in concatenation

Joseph Aloysias
Joseph Aloysias ✭✭✭✭✭
edited 02/20/23 in Formulas and Functions

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 + "|"

Tags:

Best Answers

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

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

    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!

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭

    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
    Jeff Reisman ✭✭✭✭✭✭

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

    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!

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭
    edited 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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓


    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.

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭

    @Andrée Starå this is working as expected. Thanks a lot

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Domnic Victor

    Excellent!

    Happy to help!

    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.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    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!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Jeff Reisman

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!