Merging Single & Multi Select Columns with Free Text into one Merged Column

JGray

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 & multi-select 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.

Brett Wyrick

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:

• Combine (Concatenate) Text or Values from Two or More Cells with a Simple Formula | Smartsheet Learning Center

Multi-select columns and getting text values from them

When you try and take text from a Multi-select 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 multi-select column.

Let's say you have a column named "Colors" that's a multi-select column. For an example, I've created two extra columns to show how this works. ("T/N" = "Text/Number")

1. The first column is where the selections were made.
2. The second column, a Text/Number type column, is a simple "=" formula, =[Colors (Multi Select)]@row
3. 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 multi-select columns:

Let's say you've got 5 columns. I've added the column type in parentheses.

1. Model Name (primary column)
2. Date (Date)
3. Status (single select)
4. Colors (multi-select)
5. Assignee (multi-select)

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 (multi-select)]@row, "
", ",") + "," + SUBSTITUTE([Assignee (multi-select)]@row, "
", ",") + "," + Date@row

Two things to note:

1. 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 re-enter it.
2. 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

JGray

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 E-M together, separated by commas. I tried the formula above in SmartSheet and I kept getting invalid.

I tried to use a =SUBSTITUTE([Multi-Select Column]@row, CHAR(10), ". "), but the same result kept popping up.

CSV_AssetsTest.xlsx

Brett Wyrick

Hey @JGray , I updated my initial response just a few minutes ago. Looks like I pressed "Submit" too early. Can you re-read and let me know if that works?

JGray

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

Brett Wyrick

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:

https://app.smartsheet.com/b/publish?EQBCT=1c21cc61c1314bbbbc055cb50b52c76d

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 + "," + [Sub-Category]@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!

JGray

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

Brett Wyrick

JGray's Conundrum.xlsx

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

JGray

Thanks for the help.