This is an excel formatting problem that most of us have run into at some point. You want to paste a long number like ‘17897870708707312121212’ into a cell and Excel truncates it to ‘1.79E+21’. This happens because when Excel sees long numbers, it tries to format it to the scientific notation (or the ‘E’ notation to be precise). Even if you save the file as CSV and open it up in a text editor, it will still show as ‘1.79E+21’.
This is an easy problem to fix. You need to select the cell, and then format that cell as a number with no decimal places. Here is how you do it.
- Click on the cell
- Click on Format->Cells from the top menu. You will notice that ‘Scientific’ formatting is turned on by default.
- Click on Numbers in the selection area. And set the number of decimal places to 0
Why is the string of numbers cut off an replaced by a bunch of zeros even after formatting?
Sometimes even after formatting, the sku or tracking number has been cut of and replaced by a bunch of zeros ‘17897870708707300000000’? This is because of another formatting issue due to the way Excel and other spreadsheet programs open a CSV file and display/auto-format the data within. For more information and assistance see these two links:
- Why does my exported CSV data get converted to weird formats?
- Import or export text (.txt or .csv) files
Here are the steps we suggest taking to format it correctly:
- Before opening the csv file with your default Excel format, open the CSV in a different spreadsheet application, i.e. right click on the file → Open with, i.e. NUMBERS on Mac or upload it as a google doc first to edit that column before downloading it again
- If you have already opened the csv file with the wrong format, delete this file and download it again since once it has been opened there is no going back
- Format the appropriate column to ‘Numbers’
- Export to Excel or CSV from NUMBERS or your Google Drive
- Open the excel or csv file and right click on the column for “SKU” or “Tracking Number”
- Select Format cells
- Select ‘Number’
- Move the Decimal Places to 0
- SAVE file. The correct format is saved!
As an alternative, you can
- Download the csv file onto your computer (do not open it)
- Upload the unopened file into your Google Drive
- In Google Drive open it with excel
- Download into an Excel file
For more assistance, please feel free to email firstname.lastname@example.org.