Formula to join text strings from 10 columns

Mindfull

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

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

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

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å

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

formula smartsheet.png

formula smartsheet2.png

David Joyeuse

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å

@Prajna Jain

I saw that David answered already!

Let me know if I can help with anything else!

Best,

Andrée

Mindfull

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

@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

@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

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

Mindfull

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

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)