How do I avoid an export to Excel combining multiple cell values into one?

Dave Jobling

I have a sheet that contains cells that are populated from a multi-select drop-down. Many of those cells contain two or three separate values.

When I export the sheet data to Excel, those multiple values get combined into a single value. For example:

A cell in the sheet contains three category values: Content labeling, Usability, Knowledge management.

The equivalent cell in the spreadsheet contains a single value: Content labeling Usability Knowledge management.

I start off with 15 categories to choose from. After the export, because of the many different ways the drop-down values can combine, I end up with ~40 categories. This makes my reporting meaningless.

Is there a way to stop the export from combining the values?

Paul Newcome

Try enabling text wrap to see if it is actually combining them or just not displaying the line break delimiter.

Dave Jobling

Thanks for the suggestion. Unfortunately, there's no change from enabling wrap.

Paul Newcome

So it looks like it is removing the line break.

An option may be to use a helper column in your Smartsheet that subs the line break out for a specific delimiter that won't be found in any of the entries. This will convert it to a single text string instead of multiple selections, but it should preserve the delimiter when exporting, and Excel has a pretty straightforward way of parsing out data the has a unique delimiter.

Dave Jobling

I don't think the category values are separated by line breaks to begin with. I can widen the column, and get more than one value on a line.

Applying an artificial delimiter is an interesting idea. I'll give that a go, though the column impacted by this issue is populated from a multi-select drop-down, and restricted only to the values in that drop-down. That means I can't put a delimiter in the Smartsheet source. I already tried separating the exported values in Excel using commas, but that had no impact either. Or perhaps I've misunderstood your suggestion.

Paul Newcome

Yes. Multi-select columns use a line break delimiter by default. If you enable text wrapping in your Smartsheet, you will see that each option has it's own line.

Line break is CHAR(10), so to replace it you would use something along the lines of

=SUBSTITUTE([Multi-Select Column Name]@row, CHAR(10), "delimiter of choice")

Dave Jobling

OK. The logic makes sense. Can you confirm where I would put that?

Paul Newcome

You would put this in a separate column in your Smartsheet. This way you have a delimited string IN SMARTSHEET that would then get exported to Excel. Excel can then leverage the delimiter you specified since it doesn't seem to be hanging on to the line break delimiter when exporting.

Dave Jobling

Sorry, I don't understand. I create a new column in my sheet? And then what? Where does the code snippet actually go?

Paul Newcome

The formula would go in the new column in Smartsheet.

Because CHAR(10) or the line break gets lost when exporting, we replace that in Smartsheet first.

Then when it gets exported to Excel, you have your Multi-select options in the extra column with a hard-coded delimiter that won't be lost which you can work with in Excel.

Paul Newcome

I will note though... When I put it together to provide screenshots, I found that the line break delimiter DID in fact export to Excel.

Here is the Smartsheet before and after text wrapping:

And here is the exported Excel before and after text wrapping:

So it IS exporting the line break which you may be able to leverage in Excel without having to replace it first in Smartsheet.

Paul Newcome

To use the CHAR(10) or line break as a delimiter for the "text to columns" feature in excel, you would click on the "Other" box and press CTRL+J as your delimiter.

The FIND function in Excel was also able to pick up on the CAR(10) character even without text wrapping enabled. So it SHOULD be there when you export.