Join values from multiple select column

Neil Watson

Hi

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

Paul Newcome

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

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

Protonsponge

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

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.

Protonsponge

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

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

@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

@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

He goes into the corner to cry…….

Neil Watson

Thanks @Paul Newcome!

Protonsponge

@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

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

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.