How do I keep leading zeros from an Excel file that I upload into Smartsheets?
When I upload an Excel file into Smartsheets any formatting I have for numbers does not carry over. For instance, I have 6digit stores numbers where the first several digits begin with a Zero (the same with zip codes) and it drops those "leading zeros". Also, I have formatting for phone numbers and it drops the parenthesis and the hyphens.
I have to constantly go back and copy and paste that information into the sheet which defeats the whole purpose of uploading the file from Excel.
Is there another way to keep the formatting? Is there a specific format in Excel I should be using to maintain that information? I really want to avoid manually copying and pasting information as that opens us up for a lot of human error.
Is this a problem that Data Shuttle could solve?
Answers

IF you are getting the parentheses and hyphens by using special formats in excel, I don't think those would carry over. If everything is always in the same format from excel, you could use helper columns to write a formula to get a similar format.
Something like
=("0000"+storenumber@row)
would give you 000054 where 54 is the store number in the storenumber column. You could do something similar with the phone number using the mid formula.

I'm not sure if data shuttle would fix that, need to know if you are using special formats to get the leading 0s and phone number formats..

@Samuel Mueller When I format the cell I have it formatted as text. The only time it correctly uploads into Smartsheets is when the little green arrow is there inside each cell in the top left corner. If I format a phone number like this (000) 0000000, it also does not carry over that formatting.
Also, that formula won't work because it needs to equal a 6digit number. For example, a store number might be 000001 and another might be 001234. Not all of the numbers start with four zeros, unfortunately.

@BethWork to handle the store numbers, you would need a nested IF, like so
=if(len(storenumber@row)=2,"0000"+storenumber@row, if(len(storenumber@row)=3, "000"+storenumber@row, if(len(storenumber@row)=4, "00"+storenumber@row, .......ETC.
For the phone number, if you always have 10 digits, you could use a combination of the mid formula, something like
="("+ mid(phone@row, 1,3)+")"+mid(phone@row,4,3) + "" + mid(phone@row, 5,4)
that should format your phone number how you want.
Your import works with the green arrows because excel is telling you that that number is stored as text, meaning those 0's are actually there and not just visually there.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives