How do I avoid numbers getting truncated in Excel spreadsheets?

  • Last updated:
  • ordorohowto
  • Other

If you’ve ever entered a long number into Excel, only to see it shortened to something like 1.79E+21, you’re not alone!

This happens because Excel automatically formats large numbers using scientific notation. It can be a major problem when dealing with tracking numbers, order IDs, product SKUs, or other values where every digit matters.

Fortunately, newer versions of Excel provide an option to prevent this conversion. Follow the steps below to keep long numbers from being truncated or displayed in scientific notation.

Topics

  1. Choose “Don’t Convert” when opening a CSV file
  2. What if the “Don’t Convert” option does not appear?
  3. Why are there trailing zeros in my numbers?

Choose “Don’t Convert” when opening a CSV file

When you open a CSV file containing long numbers, Excel may display the following message:

By default, Excel will perform the following data conversions in this file:

  • Convert large numbers into scientific notation

Do you want to permanently keep these conversions?

Click Don’t Convert to keep the long numbers.

This tells Excel to keep long values as text rather than convert them to numbers. This preserves all digits in tracking numbers, order IDs, etc. while preventing them from appearing in scientific notation.

Note: This option is available in newer versions of Excel, including Excel for Microsoft 365 and Excel 2024. If you do not see the message, follow the steps in the next section.

What if the “Don’t Convert” option does not appear?

If Excel does not display the data conversion message, import the CSV file and set the affected columns to Text before loading the data.

  1. Open a blank workbook in Excel.
  2. Select the Data tab.
  3. Select From Text/CSV.
  4. Choose the CSV file and select Import.
  5. In the preview window, select Transform Data or Edit.
  6. Select the column containing tracking numbers, order IDs, or other long values.
  7. Change the column’s data type to Text.
  8. Select Close & Load.

Excel will load the values as text, which helps preserve every digit.

If you are manually entering or pasting long values into a spreadsheet, format the empty cells as Text before adding the values:

  1. Select the empty cells or column.
  2. Open the Format Cells window.
  3. Select Text.
  4. Click OK.
  5. Enter or paste the values.

Note: Changing the cells to Text after Excel has already converted the numbers will not restore any lost digits.

Why are there trailing zeros in my numbers?

You may notice that Excel replaces the final digits of a long number with zeros. For example:

  • Original value: 17897870708707312121212
  • Incorrectly formatted value: 17897870708707300000000

This occurs because Excel has a built-in limit of 15 digits for numerical precision. When Excel treats a value containing more than 15 digits as a number, digits after the 15th digit may be replaced with zeros.

Changing the cell format after this happens will not restore the original value. The lost digits are no longer available in the spreadsheet.

Here’s how to correct the issue:

  1. Start with a fresh file
    • If you’ve already opened the file in Excel, delete it and download a new copy.
  2. Open the CSV and click Don’t Convert
    • This keeps long values from being converted into scientific notation.
  3. Import the columns as Text when needed
    • If the Don’t Convert option doesn’t appear, follow the steps above.
  4. Verify the values
    • Confirm that all digits appear correctly before editing or saving the file.
  5. Save your working copy as an Excel spreadsheet
    • Save the file in .xlsx format after confirming that the values are correct.