Populating a Multi-Select Column from Excel

Options

I am trying to pull my data from Excel into Smartsheet and have it to have multi-select columns.

I am attaching my Word doc with the descriptions and images to explain. I cannot figure out how to get that document to post nicely in this window. :/

I appreciate any and all help.

Teri


Best Answer

  • Teri Ann Oursler
    Answer ✓
    Options

    I finally figured out the answer.

    If your data in the Excel spreadsheet is of the form:

    Dogs, Cats, Humans

    Within Excel, you can do a find and replace of the comma with a manual line break - entered as Control Shift J in the Replace field

    This will leave the data in your cell looking like (this is one cell):

    Dogs

    Cats

    Humans

    The data will then be imported as each entry being separate and will show properly in a Muti-choice drop down when you set the column type.

    I hope this helps someone else in the future.

    Teri

Answers

  • Teri Ann Oursler
    Answer ✓
    Options

    I finally figured out the answer.

    If your data in the Excel spreadsheet is of the form:

    Dogs, Cats, Humans

    Within Excel, you can do a find and replace of the comma with a manual line break - entered as Control Shift J in the Replace field

    This will leave the data in your cell looking like (this is one cell):

    Dogs

    Cats

    Humans

    The data will then be imported as each entry being separate and will show properly in a Muti-choice drop down when you set the column type.

    I hope this helps someone else in the future.

    Teri

  • Genevieve P.
    Options

    Hi Teri,

    This is great information - thanks for posting your resolution!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Teri Ann Oursler
    Options

    You are welcome. I really hope to save someone else the hours I spent on this. :-)

  • Regine Labog
    Options

    Definitely helped me, thank you for sharing!

  • FrogHolder_01
    Options

    I'm having the same issue and am stumped. In Excel, I've replaced the existing commas with Line Breaks per Teri Ann's method (using CTRL Shift J). It looks correct in Excel:



    But when I try to paste it into a multi-select column it fails; if I single-click the field, the text copies into multiple rows. If I double-click the field, the data pastes in as a single line text string:



    Text Wrapping is definitely on for the column.

    What am I doing wrong?

  • Genevieve P.
    Options

    Hi @FrogHolder_01

    I believe the solution above is when the entire sheet is being imported into Smartsheet, versus copy/pasting values from excel into a single cell in Smartsheet.


    You can use the format you have above to paste the content into the column properties as values, like so:









    Then you can select these values in the cell individually as needed.

    I'm not sure if there is a way to directly copy/paste from one Excel cell into a multi-select Smartsheet cell and have it recognize the separate values. What I might suggest doing here is have a regular text/number column where you can paste in the values from excel with commas between the values. Then you can use a column formula to replace a comma with a break, like so:

    =SUBSTITUTE([Text/Number]@row, ", ", CHAR(10))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now