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