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!
Topics
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, after formatting, long numbers may show up with trailing zeros instead of their original digits. For example:
- Original value: 17897870708707312121212
- Incorrect number: 17897870708707300000000
This happens because Excel and other spreadsheet programs automatically reformat large numbers. For more details, refer to 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 the data correctly:
- Make sure you start with a file that has not been opened in Excel yet.
- If you have, delete the file and download it again.
- When Excel adds the trailing zeros, the correct value is lost, so avoid opening it in Excel first.
- Open the file in a different application. We have used Google Sheets or Numbers (Mac).
- Format the column as “Number” (refer to step 3 above).
- Export or download the file in Excel format. See the screenshot below for guidance.
- Open the file in Excel and follow the same steps to ensure correct formatting.
- Don’t forget to save the file!
If you need further assistance, please feel free to email support@ordoro.com.
