This is an Excel formatting problem that many of us have encountered at some point.
- You want to enter a long number like 17897870708707312121212.
- Excel shortens it to 1.79E+21.
- Example: when entering tracking numbers to upload in Ordoro.
When Excel sees long numbers, they are formatted as scientific notation (or the ‘E’ notation, to be precise). If you save the file as a CSV and open it in a text editor, the data will still display as 1.79E+21.
This is an easy problem to fix. See below how to update in three easy steps!
How to format Excel cells as numbers
1. Select the cell(s) in Excel.
2. Click on Format -> Cells from the top menu. Notice that Scientific is chosen by default.
3. Select Number as the format, and set Decimal places to 0. Click OK to Save.
Why are there trailing zeros after reformatting?
Sometimes, even after formatting, the long number will end with zeros instead of the actual numbers. For example:
- Original value: 17897870708707312121212
- Incorrect number: 17897870708707300000000
This is because Excel and other spreadsheet programs auto-format data. For details, see the links below.
- Why does my exported CSV data get converted to weird formats?
- Import or export text (.txt or .csv) files
Here’s how to format it correctly:
- Ensure you have a file that has not been opened in Excel before.
- If you have, delete the file and download it again.
- Once you open a file in Excel, and Excel adds the trailing zeros, the correct value is lost.
- Open the file in a different application. We have used Google Sheets or Numbers on Mac.
- Format the column to Number (as seen in step 3 above).
- Choose an option to Download/Export to Excel. See the screenshot below.
- Open the file in Excel and repeat the top three steps above, as necessary.
- Be sure to save the file!
If you need further assistance, please feel free to email firstname.lastname@example.org.