Excel Remove Leading Zeros from Text Without Data Loss

How-to basics · Updated November 18, 2026

To remove leading zero characters from text in Excel, use a formula in B2 and fill it down: =IF(A2="","",IF(SUBSTITUTE(A2,"0","")="","0",MID(A2,FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2),LEN(A2)))). It removes zeros only at the start, leaves other characters intact, and returns one zero when the cell contains only zeros. If the text is really a number and you want a numeric result, use a conversion method instead—but do not convert codes or identifiers whose zeros matter.

Why Excel keeps leading zeros

Excel distinguishes between a value and the way it is displayed. A cell containing text such as "00427" stores those five characters, so Excel preserves the zeros. This is useful for postal codes, product numbers, account references, and other identifiers that may look numeric but are not quantities.

A cell containing the number 427 is different. Its stored value is 427, even if a custom number format makes it appear as 00427. In that case the zeros are a display effect, not part of the value. Removing the format or selecting General can change how the value looks without changing the underlying number. That will not remove zeros from text.

Before editing a column, check whether the zeros are meaningful. If you are working with amounts, counts, or other values used in calculations, conversion to numbers is usually appropriate. For IDs and codes, it can make two distinct identifiers appear identical or discard information needed to match another system.

Choose between cleaning text and converting to numbers

There are two different jobs people often mean by “remove leading zeros.” The right method depends on the result you need:

  • Keep the result as text: use the first formula above. It removes only initial zero characters and preserves any later letters, punctuation, or zeros.
  • Make the result a number: use VALUE or Text to Columns. Excel can then calculate with the result, and ordinary number formatting will not show the discarded zeros.
  • Change only the display: change a custom number format if the underlying value is already numeric. This does not clean text values.

For example, cleaning the text "00058" as text gives "58"; converting it gives the number 58. The two may look alike in a worksheet, but the text result is not a numeric value. That distinction matters for sorting, calculations, lookups, and exports.

Remove zeros with a formula while keeping text

With the original text in A2, enter the opening formula in B2, then use the fill handle or copy and paste it down the column. The formula locates the first character that is not a zero and returns the string from that point onward. Its special case handles a value made entirely of zeros, while the blank check leaves an empty source cell empty.

Here are example results: "00058" becomes "58"; "000AB09" becomes "AB09"; "000" becomes "0"; and "AB007" stays "AB007". Because the formula removes only zeros at the beginning, zeros within a code remain untouched.

After checking the results, copy the formula cells and use Home > Paste > Paste Values if you want to replace formulas with their current results. Keep the original column until you have checked that the cleaned values are correct. To use the results elsewhere, you can also leave the formula column in place and refer to it directly.

The formula is designed for ordinary text strings, not for interpreting numbers. If a cell contains a number displayed with a custom format, Excel may already be treating it as numeric; inspect the formula bar or test with ISTEXT before choosing a text-cleaning approach. Also note that this formula treats a space as a character, not as a zero: a value beginning with spaces and then zeros will retain those spaces.

Convert numeric text to numbers with a formula

With numeric text in A2, this formula returns a number when Excel can interpret the cell as one, and returns the original value if conversion fails: =IF(A2="","",IFERROR(VALUE(A2),A2)). For instance, if A2 contains the text "00058", the result is the numeric value 58. Fill the formula down, review the results, and use Paste Values if you want fixed values rather than formulas.

This option is convenient when every entry should be numeric, but it is not a general-purpose way to clean codes. A value such as "00058A" cannot be converted by VALUE, so the formula returns the original text. Numeric-looking strings may also be interpreted according to the workbook’s regional settings—for example, decimal and thousands separators can differ.

Excel stores only about 15 significant digits of numeric precision. Long account or reference numbers should therefore remain text even if they contain only digits. Converting a long identifier can change its precision, in addition to removing its zeros. Dates and other strings that Excel recognizes as numbers can also be interpreted differently than intended, so inspect representative results before applying a conversion to a whole dataset.

Use Text to Columns for a one-time conversion

If the selected column contains numeric text that should become numbers, Text to Columns can convert it without a helper formula. On Excel for Microsoft 365 on Windows, first make a copy of the source column, select the cells, and choose Data > Text to Columns. In the wizard:

  1. Choose Delimited and select Next. Do not select a delimiter if the entries should stay in one piece.
  2. Select Next again to reach the column data format options.
  3. Choose General, confirm the destination if you do not want to overwrite the source, and select Finish.

General interprets numeric-looking entries as numbers, so text such as "00058" will normally become 58. Check the output, especially if the column contains long numbers, dates, decimal separators, or identifiers. The command can affect every selected row, so use it only when converting the entire selected range is intended. The dialog layout can differ slightly on Mac and in Excel for the web; use a copy of the data if you cannot confirm the conversion options in your version.

Why changing the cell format may not work

Changing a text cell from Text to General does not reliably re-parse its existing contents. The cell may continue to contain the same characters, including its leading zeros. Similarly, a custom format such as 00000 can add zeros to the display of a number, but it does not remove zeros stored in a text string.

To check what Excel is storing, select a cell and look at the formula bar. You can also test a value in a spare cell: with the entry in A2, =ISTEXT(A2) returns TRUE when A2 is text, and =ISNUMBER(A2) returns TRUE when it is a number. These checks help explain why two cells that look identical may behave differently in formulas or sorting.

If a numeric value merely looks padded, select it and use Home > Number Format > General to remove a custom display format. If a text value contains actual zeros, use a formula or a deliberate conversion. Formatting and data cleaning solve different problems.

Common mistakes and data checks

  • Converting identifiers by accident: a postal code or product code may need its zeros to stay. Preserve the source and confirm the business meaning before converting.
  • Assuming every entry is numeric: mixed values may include letters, spaces, or punctuation. The text formula handles mixed strings; VALUE conversion does not turn arbitrary codes into numbers.
  • Overwriting the original too soon: calculate in a new column, compare results, and only then paste values or replace the source.
  • Ignoring all-zero entries: decide whether "000" should become "0", an empty cell, or something else. The text formula above deliberately keeps one zero.
  • Expecting sorting to clean data: sorting changes row order, not the contents or type of a value.

For a quick quality check, compare the number of populated source cells with the result column and inspect examples from the top, middle, and bottom. If the data comes from another system, retain an untouched copy so you can restore original identifiers if a later lookup or import requires them.

Frequently Asked Questions

How do I remove leading zeros in Excel without a formula?
For numeric text, select the cells and use Data > Text to Columns, then choose General as the column format and finish the wizard. This converts text to numbers, so do not use it for identifiers that must retain their zeros.

Why does Excel keep the zeros after I choose General?
If the cell contains text, General formatting may not convert the existing characters into a number. Use a conversion step such as Text to Columns or VALUE, or use a text-cleaning formula if the result should remain text.

Can I remove zeros but keep the result as text?
Yes. Use the first formula in this article in a helper column. It removes zeros only from the beginning and preserves letters, punctuation, and zeros later in the string.

How do I remove zeros from only some cells?
Apply a formula to the relevant rows, or filter and select only the entries you intend to convert. Work in a copied column first, since a bulk conversion can change every selected numeric-looking entry.

Will removing leading zeros affect a lookup?
It can. Excel treats text "0058" and the number 58 as different values in many comparisons and lookups. Clean both lookup columns consistently, or preserve the zeros when they are part of the identifier.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.