Join values from multiple select column
Hi
I am looking for a formula that joins the values selected in a multi-select column as per the example below:
Best Answers
-
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 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
-
This should be what you are looking for, made a quick test to confirm and worked for me :-)
=JOIN([Multiple Value Column]@row, ", ")
-
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.
-
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.
-
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), ", ")
-
@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)
-
@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.
-
He goes into the corner to cry…….
-
Thanks @Paul Newcome!
-
@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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!