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

Dave Jobling
Dave Jobling ✭✭✭
edited 04/02/20 in Smartsheet Basics

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?

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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


    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/02/20

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com