#### 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

Options
edited 12/09/19

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!

kylie

• Options

Never mind! Just worked this one out:

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

Works like a charm!

Thanks anyway

• ✭✭✭✭✭✭
Options

Kylie

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)

Craig

• edited 02/02/17
Options

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

• Options

Hi All,

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

Formula:

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

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

Thoughts?

Thanks,
Melissa

• ✭✭✭✭✭✭
Options

Melissa,

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.

Craig

• edited 02/23/17
Options

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!

• ✭✭✭✭✭✭
Options

Melissa,

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,

should do the trick.

Craig

• Options

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

• edited 11/18/19
Options

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.

Best,

Manojlo

• ✭✭✭✭✭✭
Options

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)

Cheers,

Craig

• Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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.

Craig

This discussion has been closed.