This formula I have produces extra commas from the blank cells. Any one know how I can remove them?

=JOIN(COLLECT([Serial Number]@row:[Serial Number 20]@row, [Serial Number]@row:[Serial Number 20]@row, FIND(LEFT(@cell, 4), "Q2AX^Q2BX") > 0), ",")



Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try:

    =JOIN(COLLECT([Serial Number]@row:[Serial Number 20]@row, [Serial Number]@row:[Serial Number 20]@row, AND(FIND(LEFT(@cell, 4), "Q2AX^Q2BX") > 0, ISTEXT(@cell)), ",")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!