How to Remove Leading Zeros in Excel

How to remove leading zeros in Excel

To remove leading zeros in Excel, first check where they come from. If the value is stored as text, such as 00123 imported from a file, convert it to a number with Convert to Number or =VALUE(A2), and the zeros drop away. If the cell holds a real number and a number format is adding the zeros, change the format to General.

The two cases look identical in the cell, and each fix does nothing to the other, which is why so many attempts seem to fail. Then there is one situation where you should not remove the zeros at all.

TL;DR: Key takeaways

• Click the cell and look at the formula bar. Zeros there mean text; no zeros there mean a format.
• Text: Convert to Number, =VALUE(), or a macro on the whole column.
• Format: Ctrl + 1, Number tab, choose General.
• Converting numbers longer than 15 digits corrupts them. Leave long IDs as text.
• If zeros are meaningful, as in postal codes, keep them.

Check: text or format?

Click a cell showing 00123 and read the formula bar.

• The formula bar also shows 00123. The value is stored as text. These cells are usually left-aligned and may show a small green triangle in the corner.
• The formula bar shows 123. The cell holds the number 123, and a number format such as 00000 is padding it with zeros for display.

Microsoft explains that Excel normally strips leading zeros when it turns entries into numbers, so the zeros only survive when the value is text or a format puts them back on screen.

Remove zeros stored as text

Converting the text to a number removes the zeros, because a number has no leading zeros. Microsoft gives two built-in ways:

Convert to Number. Select the cells, click the small error indicator that appears beside them (or press Alt + Shift + F10), and choose Convert to Number.

The VALUE function. In a helper column, enter =VALUE(A2) and fill down. Then copy the results and use Paste Special > Values over the original column.

For a column that also contains currency symbols, thousands separators or stray spaces, the free Convert Text to Numbers macro handles the whole selection in one pass. It strips those characters, converts each value, and reports how many cells it converted and how many it skipped as not numeric. It leaves cells that are already real numbers alone, so it will not remove zeros created by a number format. Converting text to numbers covers the wider problem.

Remove zeros created by a number format

If the formula bar shows no zeros, the cell is a number dressed up by its format. Excel's custom codes such as 00000, or the Special formats for postal codes, deliberately pad numbers with zeros.

1. Select the cells.
2. Press Ctrl + 1 to open Format Cells.
3. On the Number tab, choose General, then click OK.

The value never changes; only the display does.

Before you remove them: long numbers and real codes

Two kinds of data should keep their zeros.

Numbers longer than 15 digits. Microsoft notes that converting long numerical data truncates it "to 15 digits of precision." A 16-digit card number or a long account number converted to a number has its last digits silently replaced with zeros. Leave these as text.

Codes where the zero means something. Postal codes, product codes and employee IDs are identifiers, not quantities. 00123 and 123 may be different records. Removing the zeros can break lookups against another system that stores them, so check before converting.

If you are tidying a column that mixes both, convert only the cells that are genuinely quantities. The same caution applies when cleaning phone numbers, where a leading zero is often part of the number.

Frequently asked questions

How do I remove leading zeros in Excel?

If the value is stored as text, convert it to a number with Convert to Number from the error indicator, or with =VALUE(A2) in a helper column. If a number format adds the zeros, select the cells, press Ctrl + 1 and choose General.

Why won't Excel remove my leading zeros?

You are probably using the fix for the other case. Converting to a number does nothing when a format adds the zeros, and changing the format does nothing when the value is text. Check the formula bar to see which one you have.

How do I remove leading zeros without a formula?

Select the cells, click the error indicator next to them (or press Alt + Shift + F10), and choose Convert to Number. For a large or messy column, a macro converts the whole selection in place.

Will removing leading zeros change my data?

It changes text into numbers, which is usually the point. Numbers longer than 15 digits lose precision when converted, so leave long IDs such as card or account numbers as text. Codes where zeros matter should also stay as text.

How do I remove leading zeros from a number stored as text, keeping it as text?

For whole-number codes, =TEXT(VALUE(A2),"0") returns the digits without the zeros, still as text. It rounds any decimals, so use it only on whole numbers, and not on values longer than 15 digits, which VALUE cannot hold exactly.

What to do next

Check the formula bar, then apply the matching fix. For a whole column of imported text numbers, run Convert Text to Numbers once rather than converting cells by hand. Data from a CSV often arrives this way; the CSV imports guide covers the other cleanup an import usually needs.