How do I keep leading zeros from an Excel file that I upload into Smartsheets?

BethWork

When I upload an Excel file into Smartsheets any formatting I have for numbers does not carry over. For instance, I have 6-digit 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?

Samuel Mueller

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.

Samuel Mueller

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

BethWork

@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) 000-0000, it also does not carry over that formatting.

Also, that formula won't work because it needs to equal a 6-digit number. For example, a store number might be 000001 and another might be 001234. Not all of the numbers start with four zeros, unfortunately.

Samuel Mueller

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