Formula to join text strings from 10 columns
I tried Join Function to add text strings from 10 different column using following but no success:
=JOIN([Region 1]:[Region 1], [Region 2]:[Region 2],[Region 3]:[Region 3],””)
Can anyone help please?
Thanks
Prajna
Answers
-
Are all of the columns next to each other?
If so, you could use
=JOIN([Region 1]:[Region 3], "")
If not, then you would use something like
=JOIN([Region 1]:[Region 1], "") + JOIN([Region 2]:[Region 2], "") + JOIN([Region 3]:[Region 3], "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
It is not working, as my fields are not adjacent to each other I tried the second option.
I tries it in following manner where I have Columns ABC 1 and XYZ 1and so on in continuation.
=JOIN([ABC1]:[XYZ1], "") + JOIN([ABC2]:[XYZ2], "") + JOIN([ABC3]:[XYZ3], "")
But this one is not working.
-
Hi Prajna,
Could you share us a screenshot of your sheet with no sensitive or confidential datas so we get a better view of what you're trying to accomplish. Paul's version should be working fine in either case.
Thanks!
-
Hi @Prajna Jain
Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
Hi Prajna,
Ok, so looking at the second picture, the formula is working fine as there's no errors within it.
If you want to only JOIN cells that are not blank, then use this:
=JOIN(COLLECT([ABC1]:[XYZ1], NOT(ISBLANK(@cell))),"") + JOIN(COLLECT([ABC2]:[XYZ2], NOT(ISBLANK(@cell))), "") + JOIN(COLLECT([ABC3]:[XYZ3], NOT(ISBLANK(@cell))), "")
-
I saw that David answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hello David,/Andrée
I tried this and my exact formula phrased as under:
=JOIN(COLLECT([Perimeter Cooling_TH]:[Chiller_TH], NOT(ISBLANK(@cell))), "") + JOIN(COLLECT([Single Phase UPS]:[Converged PS], NOT(ISBLANK(@cell))), "") + JOIN(COLLECT([SmartCabinet:SmartAisle], NOT(ISBLANK(@cell))), "")
It is throwing #UNPARSEABLE error, I am not sure if this is complete or something is missing please help.
-
First... You are missing a range from each of the COLLECT functions. You are going to want them to look more like this:
COLLECT([Column Name]:[Column Name], [Column Name]:[Column Name], criteria)
Second... You don't need the square brackets around the range in the third COLLECT:
[SmartCabinet:SmartAisle]
should be
SmartCabinet:SmartAisle
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
As suggested I rephrased it as :
=JOIN(COLLECT([Perimeter Cooling_TH]1:[Chiller_TH]1, NOT(ISBLANK(@cell))), "") + JOIN(COLLECT([Single Phase UPS]1:[Converged PS]1, NOT(ISBLANK(@cell))), "") + JOIN(COLLECT(SmartCabinet1:SmartAisle1, NOT(ISBLANK(@cell))), "")
Now I am getting #INCORRECT ARGUMENT SET error, 😔
-
It is because of your COLLECT functions. each COLLECT function should have at a minimum two separate ranges.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Tried again:
=JOIN(COLLECT([Perimeter Cooling_TH]1:[Perimeter Cooling_TH]1, [Chiller_TH]1:[Chiller_TH]1, NOT(ISBLANK(@cell))), "") + JOIN(COLLECT([Single Phase UPS]1:[Single Phase UPS]1, [Converged PS]1:[Converged PS]1, NOT(ISBLANK(@cell))), "") + JOIN(COLLECT(SmartCabinet1:SmartCabinet1, SmartAisle1:SmartAisle1, NOT(ISBLANK(@cell))), "")
now it is leaving the cell blank.....not fetching up the cell content. 🙄
-
Whichever range you are tying to join if it is not blank should be repeated in the COLLECT function.
COLLECT([Column Name]1:[Column Name]1, [Column Name]1:[Column Name]1, criteria)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!