Issue combining multiple cells in one sheet


I am trying to combine 4 numerical cells in a single sheet into another cell/different column in the same sheet. When i do this it is creating a sum of the first two cells and combining the rest.

1 1 00614 100

Outcome is 200614100

Formula =JOIN([Grouper 1 (Entity)]@row + [Grouper 2 (Type)]@row + [Grouper 3 (Numerical Workday Center # - 5 Digits)]@row + [Start Ticker # (Always Starts With 100)]@row)

I do not understand why it would add the first two and then combine the others. Each column is set to text/number

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @mcaulism

    The JOIN syntax is JOIN(range, delineator) .

    In your formula, the range is something like 123+455+ABC, so the formula first gets the 123+456= 569 and then combines it with ABC. 😀

    To treat Number+Number to act like NumberNumber or 123+456=123456, you can add "" as =123+""+456.

    Or, if you change the formula to JOIN([Grouper 1 (Entity)]@row:[Start Ticker # (Always Starts With 100)]@row), the JOIN function will treat all the values in the [Grouper 1 (Entity)]@row:[Start Ticker # (Always Starts With 100)]@row range as text, and you will get the desired result like 12345ABC.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!