How to Stop Excel From Removing Leading Zeros

To stop Excel from removing leading zeros, format the cells as text before you type: select the cells, go to Home, open the Number Format list in the Number group, and choose Text. Anything you enter afterward, such as 00123 or 02134, stays exactly as typed. For a single entry, type an apostrophe first (‘00123). If the numbers are already in the sheet without their zeros, use a custom number format or the TEXT function to put them back.

Applies to: Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac, plus Excel for the web where noted. Checked against Microsoft Support on October 7, 2026.

Why Excel removes leading zeros

When you type something that looks like a number, Excel stores it as a number so it can be used in formulas. A number has no use for zeros in front of it, so 00123 becomes 123. That is fine for prices and quantities, but it breaks values that are really codes made of digits: ZIP codes such as 02134, employee or student IDs, product codes, account numbers and phone numbers. These values are never added up, so they should be stored as text.

The same conversion happens when you paste data from another program or open a CSV file by double-clicking it. Microsoft also notes that Excel keeps only 15 significant digits in a number, so a long ID such as a 16-digit card or tracking number loses its last digits as well as its leading zeros unless it is stored as text.

Which method should you use?

Situation	Best method	Result
You are about to type or paste a column of codes	Format the cells as Text first	Stored as text, zeros kept exactly
You only need one or two cells	Type an apostrophe before the number	Stored as text, apostrophe hidden in the cell
Every code has the same length (for example, 5 digits)	Custom number format such as 00000	Stays a number, zeros shown on screen
US ZIP codes	Special format: Zip Code or Zip Code + 4	Stays a number, shown as a ZIP code
The zeros are already gone and you need text	TEXT function, such as =TEXT(A2,”00000″)	Text copy with zeros in a new column
You import CSV or text files	Data > From Text/CSV and set the column to Text	Imported column kept as text
You want Excel to stop doing it everywhere	Automatic Data Conversion option (Microsoft 365 and Excel 2024)	Leading zeros kept when typing, pasting and opening files

Method 1: Format the cells as Text before entering data

This is the most reliable fix because the value is stored exactly as you type it. It works the same way in Excel for Windows and Excel for Mac.

1. Select the cells. Click a column letter to select the whole column, or drag across the empty cells where the codes will go. Do this before you type the data.
2. Open the Number Format list. On the Home tab, in the Number group, click the arrow next to the Number Format box (it usually shows General).
3. Choose Text. Click Text in the list. If you don’t see it, scroll to the bottom of the list. The box now shows Text for the selected cells.
4. Type or paste the numbers. Enter values such as 00123. They stay as typed and line up on the left side of the cell, which is how Excel displays text.

You can reach the same setting from the Format Cells dialog: press Ctrl+1 (Cmd+1 on a Mac), select Text on the Number tab, and click OK.

Important: applying Text to cells that already contain numbers does not bring back zeros Excel has already removed. Microsoft states that the Text format only affects numbers entered after it is applied. Retype those values, or use Method 4 or Method 5.

In Excel for the web, select the cells, right-click inside them, choose Format Cells, pick Text as the category, and then enter or paste the numbers.

Method 2: Type an apostrophe before the number

For an occasional entry, type an apostrophe and then the number, for example ‘00123, and press Enter. Microsoft documents that Excel treats an entry that starts with an apostrophe as text. The cell shows 00123, and the apostrophe itself does not appear in the cell.

This is quick, but you have to remember it for every cell. For a whole column, Method 1 is less error-prone.

Method 3: Use a custom number format for fixed-length codes

If every code has the same number of digits and you would like the values to stay numbers (for sorting numerically or using them in lookups against other numbers), a custom format pads them with zeros on screen.

1. Select the cells and press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells.
2. On the Number tab, select Custom in the Category list.
3. In the Type box, enter one zero for each digit the code should have. For a 5-digit code, type 00000. You don’t need quotation marks.
4. Click OK. A cell containing 123 now displays 00123.

The zeros are part of the display only. The cell still holds the number 123, so if you copy the value into another program or a text field, the zeros may not come with it. Custom formats can also add separators, for example 000-00-0000 for a nine-digit identifier. Codes longer than 15 digits must be stored as text instead (Method 1 or 2).

Method 4: Apply the Zip Code format

Excel has a built-in format for US postal codes:

1. Select the cells that hold ZIP codes.
2. On the Home tab, click the Dialog Box Launcher (the small arrow) next to Number.
3. In the Category box, click Special.
4. In the Type list, click Zip Code or Zip Code + 4, and then click OK.

These options appear only when the Locale (location) setting in the dialog is English (United States). Like a custom format, the Special format changes how the number looks, not what is stored.

Method 5: Rebuild the zeros with the TEXT function

When you need real text values with the zeros included (for example, to export a clean list or join the code with other text), add a helper column with a formula. If the shortened codes are in column A starting at A2, type this in B2 and fill it down:

=TEXT(A2,”00000″)

The number of zeros in quotes sets the total length, so 123 becomes 00123 and 2134 becomes 02134. Microsoft’s own example, =TEXT(1234,”0000000″), returns 0001234. The format code must be inside quotation marks.

The result is text, which Microsoft warns can make it harder to use in later calculations. Keep the original column for math and use the TEXT column for display or export. To replace the original values, copy the helper column and paste it back as values, then delete the helper column. For more ways to turn numbers into text, see our guide on converting a number to text in Excel.

Method 6: Keep zeros when importing a CSV or text file

Double-clicking a CSV file opens it with automatic conversion, which strips the zeros before you can do anything. Import it with Power Query instead so you can set the column type first:

1. On the Data tab, in the Get & Transform Data group, click From Text/CSV.
2. Select the file and click Import. A preview window opens.
3. Click Transform Data to open the Power Query Editor.
4. Click the header of the column that holds the codes. On the Home tab, open Data Type and choose Text.
5. If a Change Column Type message appears, click Replace Current.
6. Click Close & Load. The data loads into Excel with the zeros intact.

When the source file changes, click Data > Refresh and the same Text setting is applied again. Our guide to importing a CSV file into Excel covers the rest of the import options.

Method 7: Turn off automatic removal of leading zeros

Excel for Microsoft 365 and Excel 2024 (Windows and Mac) have an Automatic Data Conversion setting that controls this behavior for the whole app.

• Windows: click File > Options > Data, then find Automatic Data Conversion.
• Mac: click Excel > Preferences > Edit, then find Automatic Data Conversion.

Clear the check box for removing leading zeros and converting to a number, then click OK. According to Microsoft, this setting affects typing, pasting from external sources, opening .csv and .txt files, Find and Replace, and the Convert Text to Columns Wizard. It does not apply to Power Query imports, which use their own data types (Method 6). The same section has related options for long numbers shown in scientific notation and for text that Excel turns into dates. Older versions such as Excel 2021 don’t have this setting, so use Methods 1 to 6 there.

How to confirm the zeros are kept, or undo the change

• Check the formula bar. Click a cell. If the formula bar shows 00123, the zeros are stored. If it shows 123 while the cell shows 00123, a custom or Special format is padding the display.
• Check the alignment. With default alignment, text sits on the left of the cell and numbers on the right.
• Undo a format. Select the cells and choose General in the Number Format list. Values typed as text stay text until you convert them (see the next section).
• Undo the app setting. Select the Automatic Data Conversion option again to restore Excel’s default behavior.

Troubleshooting

The zeros were already removed

Changing the format after entry does not restore them. Retype the values in cells formatted as Text, apply a custom format with the right number of zeros, or rebuild them with =TEXT(A2,”00000″). If the codes came from a file, re-import the original file with Method 6 rather than repairing the damaged copy.

A green triangle appears in the corner of the cells

Excel marks possible errors with a triangle in the top-left corner of the cell. For codes stored as text, the triggering rule is “Numbers formatted as text or preceded by an apostrophe.” Select the cell, click the error indicator, and choose Ignore Error. Do not choose Convert to Number, because that removes the zeros again. To stop the flag for all workbooks, go to File > Options > Formulas and clear that rule under Excel checking rules.

Long numbers turn into E+ or end in zeros

Excel keeps only 15 significant digits. A 16-digit or longer value is rounded and digits after the fifteenth become zeros, and long numbers may be shown in scientific notation such as 1.23E+15. Format the cells as Text before entry or use an apostrophe. A custom format cannot help because the digits are already lost. If you see E+ on shorter numbers, our guide on getting rid of E+ in Excel explains the display fix.

Formulas or SUM ignore the text-stored numbers

Microsoft notes that numbers stored as text can give unexpected results in calculations. That is expected for codes, which you should not add up. If a column contains real quantities that were stored as text by mistake, select the cells, click the error indicator and choose Convert to Number.

Zip Code is missing from the Special list

The Special category shows US formats only when Locale (location) is set to English (United States). Choose that locale in the same dialog, or use a custom format such as 00000.

Codes turn into dates instead

Some entries are converted to dates rather than losing zeros; Microsoft’s example is 12/2, which changes to 2-Dec. Formatting the cells as Text first prevents both problems; see our guide on stopping Excel from changing numbers to dates.

Work or school files behave differently

Automatic Data Conversion is an Excel option, not a workbook feature, and older versions don’t have it, so a colleague may still see zeros disappear when they type or paste into a file you share. For shared workbooks, format the code columns as Text in the file itself before anyone enters data.

Frequently asked questions

Why does Excel remove the 0 at the start of a number?

Excel treats anything that looks like a number as a number, and leading zeros don’t change a number’s value, so they are dropped. Storing the entry as text keeps every character.

How do I keep leading zeros when I paste data into Excel?

Format the destination cells as Text before you paste. In Microsoft 365 or Excel 2024 you can also turn off the leading-zero option under Automatic Data Conversion, which covers pasting from external sources.

Can I add leading zeros to numbers already in the sheet?

Yes. Apply a custom number format such as 00000 to show them, or use =TEXT(A2,”00000″) in a new column to create text values that include the zeros.

Will numbers stored as text still sort correctly?

Usually, if the codes all have the same length, such as five-digit ZIP codes. Microsoft notes that numbers stored as text can produce confusing sort orders, which you are most likely to see when codes of different lengths are mixed in one column.

Does Excel for the web keep leading zeros?

Yes, if you format the cells as Text first: right-click the selection, choose Format Cells, pick Text, then enter or paste the numbers.

Why does my ZIP code show 2134 instead of 02134?

The cell holds a number. Apply the Zip Code format (Method 4) or a 00000 custom format to display it with the zero, or retype it in a cell formatted as Text.

For a column you’re about to fill, set it to Text now. For the full list of options and limits, see Microsoft’s guide to keeping leading zeros and large numbers and its page on setting automatic data conversions.

Related: how to print addresses on envelopes from Excel.