Skip to content
ArumSoft Learning
KO
    1.2E+15Excel

    Why Excel changes long numbers to E+ (and drops digits)

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerExcel keeps only 15 significant digits, so any digits after the 15th become 0 and can't be recovered from the cell. Format the column as Text before you type or paste, or type an apostrophe first.

    Causes and fixes

    1. General format shows long numbers in scientific notation
    2. Digits after the 15th are replaced by zeros
    3. The values came from a CSV file
    4. Lookups and counts on long numbers go wrong

    1General format shows long numbers in scientific notation

    In the General format, numbers with 12 or more digits show as scientific notation, such as 1.23457E+11. The full number is still stored if it has 15 digits or fewer.

    Fix

    Select the cells, press Ctrl+1, choose Number with 0 decimal places, or Custom with the format 0.

    2Digits after the 15th are replaced by zeros

    Excel stores numbers with 15 digits of precision. A 16-digit card number entered as a number keeps its first 15 digits, and the last one becomes 0. Formatting can't bring it back.

    How to recognise it

    The value ends in 0 even though the original didn't, and the formula bar shows the same zeros.

    Fix

    Format the column as Text first, then enter or paste the values again from the source. Typing an apostrophe before each number also works.

    Rule of thumb. IDs, card numbers and account numbers aren't quantities. Store them as text.

    3The values came from a CSV file

    Opening a CSV file directly converts long IDs to numbers and cuts them to 15 digits.

    Fix

    Use Data > From Text/CSV > Transform Data, set the column type to Text, then load it. In recent Microsoft 365 versions, File > Options > Data > Automatic Data Conversion also has an option to keep long numbers as text instead of scientific notation.

    4Lookups and counts on long numbers go wrong

    Text IDs don't match number IDs, and COUNTIF compares long numbers on 15 digits only.

    Fix

    Keep both lists as text, and compare exactly with =SUMPRODUCT(--(A2:A100=D2)). See COUNTIF counting wrong.

    Still stuck?

    • If the digits are already zeros, the only fix is to bring the values in again from the original file.
    • Numbers in quotes in a CSV file are still converted when you double-click the file. Import it instead.