Formula to join text strings from 10 columns

Mindfull · 07/07/20

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

Paul Newcome · 07/07/20

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], "")

Mindfull · 08/12/20

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.

David Joyeuse · 08/12/20

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!

Andrée Starå · 08/12/20

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!

Mindfull · 08/12/20

formula smartsheet.png

formula smartsheet2.png

David Joyeuse · 08/12/20

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))), "")

Andrée Starå · 08/15/20

@Prajna Jain

I saw that David answered already!

Let me know if I can help with anything else!

Best,

Andrée

Mindfull · 10/14/20

https://community.smartsheet.com/discussion/comment/259472#Comment_259472

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.

Paul Newcome · 10/14/20

@Prajna Jain

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

Mindfull · 10/14/20

@Paul Newcome

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, 😔

Paul Newcome · 10/14/20

It is because of your COLLECT functions. each COLLECT function should have at a minimum two separate ranges.

Mindfull · 10/14/20

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. 🙄

Paul Newcome · 10/14/20

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)

Formula to join text strings from 10 columns

Answers

Help Article Resources

Categories