Join values from multiple select column

Options

Hi

I am looking for a formula that joins the values selected in a multi-select column as per the example below:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    First we need to find out how many spaces (or in this case line breaks) there are in the string. We can do that by taking the full character count and then subtracting from it the character count of the string after stripping all line breaks out.

    =LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), ""))

    Now that we know how many there are, we can leverage the fourth portion of the SUBSTITUTE function to swap out only the last line break with the " & " using the number we got from the above.

    =SUBSTITUTE([Column Name]@row, CHAR(10), " & ", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), "")))

    From there we use another SUBSTITUTE to swap the remaining line breaks for commas, and that should get you what you are looking for.

    =SUBSTITUTE(SUBSTITUTE([Column Name]@row, CHAR(10), " & ", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), ""))), CHAR(10), ", ")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @Neil Watson It looks like we were typing at the same time. Take a look at my last comment for a method that should get rid of those helper columns.

Answers

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    This should be what you are looking for, made a quick test to confirm and worked for me :-)

    =JOIN([Multiple Value Column]@row, ", ")

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    Thanks @Protonspounge but I need the "&" when it is 2 options, and the comma and "&" for the 3 and 4 options exactly as shown in the diagram above.

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    My apologies for the overly, simplistic answer and missing the intent of your original Q… this is bugging me now I cant work it out.

    @Paul Newcome

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    First we need to find out how many spaces (or in this case line breaks) there are in the string. We can do that by taking the full character count and then subtracting from it the character count of the string after stripping all line breaks out.

    =LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), ""))

    Now that we know how many there are, we can leverage the fourth portion of the SUBSTITUTE function to swap out only the last line break with the " & " using the number we got from the above.

    =SUBSTITUTE([Column Name]@row, CHAR(10), " & ", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), "")))

    From there we use another SUBSTITUTE to swap the remaining line breaks for commas, and that should get you what you are looking for.

    =SUBSTITUTE(SUBSTITUTE([Column Name]@row, CHAR(10), " & ", LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), ""))), CHAR(10), ", ")

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    @Protonspounge I managed to work something out but it looks pretty grim. I had to use several helper columns to parse out the multi select values and then put them back together. I was then able with the help of ChatGPT to work out how to do the comma and & formatting.

    =IF(FIND("Option 1", [Multi Select Column]@row) > 0, "Option 1", "")

    =IF(FIND("Option 2", [Multi Select Column]@row) > 0, "Option 2", "")

    =IF(FIND("Option 3", [Multi Select Column]@row) > 0, "Option 3", "")

    =IF(FIND("Option 4", [Multi Select Column]@row) > 0, "Option 4", "")

    =JOIN(COLLECT([Helper Option 1]@row:[Helper Option 4]@row, [Helper Option 1]@row:[Helper Option 4]@row, @cell <> ""), ", ")

    =IF(COUNTM([Helper Option 1]@row:[Helper Option 4]@row) > 1, LEFT(Combi@row, FIND(">", SUBSTITUTE(Combi@row, ",", ">", LEN(Combi@row) - LEN(SUBSTITUTE(Combi@row, ",", "")))) - 1) + " &" + RIGHT(Combi@row, LEN(Combi@row) - FIND(">", SUBSTITUTE(Combi@row, ",", ">", LEN(Combi@row) - LEN(SUBSTITUTE(Combi@row, ",", ""))))), Combi@row)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @Neil Watson It looks like we were typing at the same time. Take a look at my last comment for a method that should get rid of those helper columns.

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    He goes into the corner to cry…….

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options
  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    @Paul Newcome - What an answer! Epic…

    @Neil Watson - Great question, learnt a lot from it from the help of Paul! I'll join you in the corner with the quality of my 1st answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️

    And to be fair… I already had this figured out some years ago, so it isn't like I just pulled this one out of thin air. I can't remember how long exactly it took me to figure out when I initially did, but it took some time, and I definitely had some helper columns going for different pieces of it.

    I actually have a workspace where I keep copies of some of my more complex solutions as a sort of reference guide so that I don't have to keep trying to figure out the logic from scratch every time. A "little black book" of sorts that I've built up over the years. Hahaha.

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    Sounds like a great idea! I have never used the "4th part" of the substitute function so that was a new one for me, and I forgot about the Char(10) separator as well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!