JOIN formulas

Kylie D'Costa

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

Kylie D'Costa

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

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

Kylie D'Costa

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

Melissa Fader1

Hi All,

 

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

 

Formula: 

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

 

Thoughts?

 

Thanks,
Melissa

J. Craig Williams

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

 

Melissa Fader1

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

Melissa,

 

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.

 

Craig

Melissa Fader1

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

 

Manojlo

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

 

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)

Cheers,

Craig

Manojlo

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

Manojlo

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

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