Populating a Multi-Select Column from Excel

Teri Ann Oursler

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

Smartsheet Help Needed.docx

Teri Ann Oursler

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

Teri Ann Oursler

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.

Hi Teri,

This is great information - thanks for posting your resolution!

Teri Ann Oursler

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

Regine Labog

Definitely helped me, thank you for sharing!

FrogHolder_01

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.

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