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

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

  • 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
    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å
    Andrée Starå ✭✭✭✭✭✭

    Hi@Prajna Jain

    Can you maybe share somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

    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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35

    随时联系我帮助内容, integrations, general workflow advice, or something else entirely.

  • David Joyeuse
    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å
    Andrée Starå ✭✭✭✭✭✭

    @Prajna Jain

    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:[email protected]| P: +46 (0) - 72 - 510 99 35

    随时联系我帮助内容, integrations, general workflow advice, or something else entirely.

  • Hi Prajna,<\/p>

    Ok, so looking at the second picture, the formula is working fine as there's no errors within it.<\/p>

    If you want to only JOIN cells that are not blank, then use this:<\/p>

    =JOIN(COLLECT([ABC1]:[XYZ1], NOT(ISBLANK(@cell))),"") + JOIN(COLLECT([ABC2]:[XYZ2], NOT(ISBLANK(@cell))), "") + JOIN(COLLECT([ABC3]:[XYZ3], NOT(ISBLANK(@cell))), "")<\/p>","bodyRaw":"[{\"insert\":\"Hi Prajna,\\n\\nOk, so looking at the second picture, the formula is working fine as there's no errors within it.\\nIf you want to only JOIN cells that are not blank, then use this:\\n=JOIN(COLLECT([ABC1]:[XYZ1], NOT(ISBLANK(@cell))),\\\"\\\") + JOIN(COLLECT([ABC2]:[XYZ2], NOT(ISBLANK(@cell))), \\\"\\\") + JOIN(COLLECT([ABC3]:[XYZ3], NOT(ISBLANK(@cell))), \\\"\\\")\\n\"}]","format":"rich","dateInserted":"2020-08-12T07:26:35+00:00","insertUser":{"userID":115578,"name":"David Joyeuse","url":"https:\/\/community.smartsheet.com\/profile\/David%20Joyeuse","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2021-08-19T14:05:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/259472#Comment_259472","embedType":"quote"}"> 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
    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

  • @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
    Paul Newcome ✭✭✭✭✭✭

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

  • 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
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/69645/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/69645/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions