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
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
Comments
-
Never mind! Just worked this one out:
=[Job Number]1 + "," + " " + [Assigned To]1 + "," + " " + [Assign To 2]1
Works like a charm!
Thanks anyway
-
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
-
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.
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 -
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
-
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!
-
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
-
PERFECT! Thanks Craig! You're a life saver!
-
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
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives