Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

JOIN formulas

Kylie D'Costa
edited 12/09/19 in Archived 2017 Posts

Hi team,


Just discovered JOIN formaula - very helpful!


However, I can't seem to get it to join THREE coloumns together. 


I'm using this formaula for joining two which is working perfect:

=JOIN([Quantity (m3)]1:[Time required]1, "-")


However, when I try and add in another (so a third coloumn) it errors.

=JOIN([Quantity (m3)]1:[Time required]1:[Boral O/N]1, "-")     I get #UNPARSEABLE


Can it not deal with a third???? If not, are there any other solutions?


Thanks guys!



  • Never mind! Just worked this one out:


    =[Job Number]1 + "," + "  " + [Assigned To]1 + "," + "  " + [Assign To 2]1


    Works like a charm!

    Thanks anyway

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    If you want to go back to the JOIN(), the argument is a range


    =JOIN([Quantity (m3)]1:[Boral O/N]1, "-")


    will pick up all the columns (inclusive) beween [Quantity (m3)] and [Boral O/N],

    whether that is 2 or 200.

    (I haven't tried more than half-dozen)



  • Kylie D'Costa
    edited 02/02/17

    That explains it - they are not next to each other! Thanks heaps Craig

  • Hi All,


    I'm trying to do something similar but I'm getting a weird result.



    =JOIN([Date]:[Marketer / Lead Initials], "-")

    It's meant to cover 5 columns of data and this is the result I get:


    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------2017-Eloqua-lookbook-GBU: Real-World Insights-AF--------------------------------------------------





  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    Blanks are not ignored by the JOIN()


    =JOIN(Nation93:[Flag Color 1]94, "+++")


    would show


    United Kingdom++++++United States+++


    if the second ([Flag Color 1]) column was blank.




  • Melissa Fader1
    edited 02/23/17

    Hi Craig,


    Thanks for getting back to me!


    The problem is...there are no blanks.  The columns are all right next to each other and there are no blanks:


    I'm obviously missing something!



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    Your equation:


    =JOIN([Date]:[Marketer / Lead Initials], "-")


    is looking at the whole columns, not just one part.

    In my example in the previous comment, there are 4 cells - 2 rows by 2 columns.

    Row numbers are 93 aand 94.


    If your data is on row 23, 


    =JOIN([Date]23:[Marketer / Lead Initials]23, "-")


    should do the trick.



  • PERFECT! Thanks Craig! You're a life saver!


  • Manojlo
    edited 11/18/19

    Hello all, i have also problem with setting this to work, i follow everything but dosen't work for me, please see attached image. What i need is simple combine cell content without any separation sign, just combine numbers from cells. Thank you in advance.






  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    JOIN's first argument is a range.

    Smartsheet's ranges do not accommodate what you are trying to do (three non-contiguous cells)

    You will need to just concatenate them together

    ="" + [ID1]@row + [ID2]@row + [ID3]@row

    note that I replaced your column names with something shorter

    The "" at the beginning will force Smartsheet to treat the numbers in your ID columns as text. Without it, you will end up with the sum of the numbers (9 in your example)



  • Thank you Craig for fast respond, it's work like a charm. :) Thank you!

  • HI Craig, i have question if you please may assist. I need some help to set up my smartsheet document. Please check photo i attached, so i have those 2 vertical column, in one column i write all Colors i need which should be picked manually and in other vertical column ID it should automaticly write number for that color. Friend of mine told me it can be done by function IF but i don't know how to set that. If anyone can help me will be much appreciate. Thanks.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Hello Manojlo,

    Your question is interesting, but it has nothing to do with the original post. Please start a new discussion thread. If I see it and have time later today or tomorrow, I will try to respond. I have some thoughts but do not wish to derail this current thread.


This discussion has been closed.