Merging Single & Multi Select Columns with Free Text into one Merged Column
I am in the midst of creating a giant SmartSheet for exporting to CSV for a client of mine. I am inputting keywords and metadata in single & multiselect columns, along with any miscellaneous keywords that I can create in another column.
I would like to merge all of these columns together into one separate column that has comma delimiters in them. I am new to creating functions, but I thought that a JOIN or = with a SUBSTITUTE thrown in there for adding commas would work, but so far I keep getting INVALID OPERATION whenever I try to merge them all together.
I could really use some help here, so any and all help is appreciated.
Answers

Hey JGray,
Just so you know, each Smartsheet cell has a limit of 4,000 characters. If this process is going to have cells with more than 4,000 characters, it'll fail. Figured I should bring that up first.
It'd be great if you could provide a link to a copy of your sheet (it's a little hard to wrap my brain around). But I'll try and get an answer to you based on what you've written.
First of all, what you're trying to do here is equivalent to "CONCATENATE" in Excel. Here's a link to a Smartsheet article that shows how to concatenate:
Multiselect columns and getting text values from them
When you try and take text from a Multiselect column via a formula, it appears like it just uses a space (" ") between the selections. Smartsheet actually uses a "line break" (aka "soft return") to differentiate between values for a multiselect column.
Let's say you have a column named "Colors" that's a multiselect column. For an example, I've created two extra columns to show how this works. ("T/N" = "Text/Number")
 The first column is where the selections were made.
 The second column, a Text/Number type column, is a simple "=" formula,
=[Colors (Multi Select)]@row
 The third column, a Text/Number type column, is the value of the first column, pasted in via "Paste special" (CTRL+Shift+V), pasting in the value. When you click to see within the cell, you'll see it has a linebreak, which I've highlighted in blue. That linebreak "character" is what you're going to need to use for the SUBSTITUTE function you're trying to run. You can select it as I have above and Copy it. ALT+Enter is not a substitute for it.
You can also double click a cell that doesn't have a formula (or F2), then press ALT+Enter within a cell. From there you can copy/paste this "line break" character.
Word of caution:
Getting this "line break character" in formulas to substitute with another character is super finicky in Smartsheet. I've been trying to work with it and it keeps getting replaced with nothing when I update the formula via clicking on additional cells. I believe Smartsheet seems to think it's a space and replaces it. If I handwrite the formula, I don't have an issue.
Formula Example to get text from multiselect columns:
Let's say you've got 5 columns. I've added the column type in parentheses.
 Model Name (primary column)
 Date (Date)
 Status (single select)
 Colors (multiselect)
 Assignee (multiselect)
Now you add a sixth column, which we'll name "All Together Now". Here's the formula you'd need to have for each cell's value to be separated by commas:
=[Model Name]@row + "," + [Status (single select)]@row + "," + SUBSTITUTE([Colors (multiselect)]@row, " ", ",") + "," + SUBSTITUTE([Assignee (multiselect)]@row, " ", ",") + "," + Date@row
Two things to note:
 Notice the linebreak in there twice on each SUBSTITUTE function. When you click on another cell while editing a formula, that linebreak character disappears and you'll need to reenter it.
 Notice the
+ "," +
between each cell. This is needed to add the commas you're wanting.
Here's a screenshot:
And the formula within the cell "All Together Now":
If this answered your question, please mark this as "Answered" to best help the community; it helps others find solutions they're searching for (and Google searches).
Thanks,
Brett
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Oh, I won't have 4K characters per line: maybe 5K total lines, but not characters. I can share the Excel version of this, since everything is locked down.
I want to combine columns EM together, separated by commas. I tried the formula above in SmartSheet and I kept getting invalid.
I tried to use a =SUBSTITUTE([MultiSelect Column]@row, CHAR(10), ". "), but the same result kept popping up.

Hey @JGray , I updated my initial response just a few minutes ago. Looks like I pressed "Submit" too early. Can you reread and let me know if that works?
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

I"ve tried and tried with that syntax and I keep getting unparceable. I'm stuck for now, but thanks for the help.

My guess is something is changing the "line break" character when you're copying/pasting. I just tried taking the text I have in the codeblock above and paste it into a formula on a sheet I'm working on, and it didn't work either.
We're not giving up that easily!
I took your Excel file, converted it to Smartsheet and I've uploaded it at this public link here:
I've successfully gotten the linebreak substituted in this formula on the first column. Take a look.
Here's the formula not in a code block, just in case that theory is correct and it's removing formatting:
=Key@row + "," + description@row + "," + [attachment filename]@row + "," + name@row + "," + Category@row + "," + [SubCategory]@row + "," + SUBSTITUTE(Commercial@row, "
", ",") + SUBSTITUTE([Product Tags]@row, "
", ",") + "," + [Finish Color]@row + "," + [Color Temp]@row + "," + [Handwritten Tags]@row + "," + tags@row
If this worked for you, let me know! And mark the answer as solved! This has been a tough one!
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

I think we're going to go back to Excel on this one.

@JGray Gotcha. Well, if you need it, here's your Excel file with everything concatenated and with commas, which would work for what you're going for, I believe.
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Thanks for the help.
Help Article Resources
Categories
Check out the Formula Handbook template!