Skip to content
ArumSoft Learning
KO
    00123Excel

    How to keep leading zeros in Excel

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

    Quick answerType an apostrophe first: '00123. For a whole column, select it and set Home > Number Format > Text before you type or paste.

    Ways to keep the zeros

    1. Format the column as Text first
    2. Type an apostrophe before the value
    3. Use a custom number format
    4. Rebuild zeros that are already gone
    5. Import CSV files with the column set to Text

    1Format the column as Text first

    Excel converts anything that looks like a number into a number, and numbers have no leading zeros. A column formatted as Text keeps exactly what you type.

    Fix

    Select the column, choose Home > Number Format > Text, then type or paste the values. Formatting after the zeros are gone doesn't bring them back.

    Use this for IDs, part numbers and phone numbers: values you never calculate with.

    2Type an apostrophe before the value

    An apostrophe tells Excel to store the entry as text. It doesn't show in the cell.

    Fix

    Type '00123. The green triangle that appears only warns that a number is stored as text; you can ignore it.

    3Use a custom number format

    If the values must stay numbers (for sorting or lookups against numbers), keep them as numbers and only change how they look.

    Fix

    Select the cells, press Ctrl+1, choose Custom and type one 0 for each digit, such as 00000 for 5-digit ZIP codes. 123 then shows as 00123.

    4Rebuild zeros that are already gone

    If every value should have the same number of digits, you can add the zeros back with a formula.

    Fix

    =TEXT(A2,"00000")
    =RIGHT("00000"&A2,5)

    Both return text. Copy the results and use Paste Special > Values to replace the originals.

    5Import CSV files with the column set to Text

    Opening a CSV file by double-clicking it lets Excel convert every column, so zeros disappear on the way in.

    Fix

    Use Data > From Text/CSV, click Transform Data, then right-click the column heading and choose Change Type > Text before you load it.

    In recent Microsoft 365 versions you can also turn this off for good: File > Options > Data > Automatic Data Conversion, then clear the option that removes leading zeros.

    Still stuck?

    • Values stored as text don't match numbers in lookups. If VLOOKUP returns #N/A, see VLOOKUP not working.
    • Phone numbers with spaces or dashes (such as 010-1234-5678) already stay as text.
    • Long numbers like card numbers lose their last digits as well. See Long numbers turn into E+.