Populating a Multi-Select Column from Excel
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
-
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
-
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
-
Hi Teri,
This is great information - thanks for posting your resolution!
-
You are welcome. I really hope to save someone else the hours I spent on this. :-)
-
Definitely helped me, thank you for sharing!
-
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?
-
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
