How to Delete Leading Zeros in Excel Without Losing Data

How-to basics · Updated November 20, 2026

To delete leading zeros in Excel, first check whether they are part of a text value or only displayed by a number format. For text that contains numbers, convert the cells to numbers with Data > Text to Columns or use =VALUE(A2) in a helper column. If the zeros appear only because of formatting, change the cell format to General. Keep values such as account numbers, postal codes, and product IDs as text when their zeros are meaningful.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, and the workbook or a copy of it.
  • The range of cells you want to change. If the data matters, make a duplicate of the sheet or copy the original column first.
  • A decision about whether the values represent quantities or identifiers. Numbers can lose leading zeros; identifiers often need to retain them.

Step-by-Step

  1. Check how the zeros are stored. Click a cell that appears to have leading zeros and look at the formula bar. If the cell shows 00123 in the formula bar, it is likely stored as text. If the formula bar shows 123 while the grid displays 00123, the cell is probably a number with a format that adds zeros. You can also select the cell and check the Number Format list on the Home tab. This check matters because changing a format will not convert text, and converting text will not necessarily remove a display format.
  2. If the zeros are only displayed, change the number format. Select the affected cells, then go to Home > Number Format and choose General. The display should change from something like 00123 to 123, while the underlying numeric value remains 123. To inspect a specific custom format, press Ctrl+1 on Windows to open Format Cells, choose the Number tab, and select General. On a Mac, use Command+1 to open Format Cells. Applying General is usually the simplest fix for a custom format such as 00000.
  3. If the values are text, select the full range before converting. Select only the cells you intend to change, including the whole column range if appropriate. If the range includes a heading, either exclude it or confirm the wizard will treat it as a heading. Check nearby columns before proceeding: converting a selected range in place overwrites those cells, so avoid selecting data you need to preserve.
  4. Convert text numbers with Text to Columns. With the text-number cells selected, go to Data > Text to Columns. In the wizard, choose Delimited and select Next. On the delimiter screen, leave the delimiter boxes unchecked unless the cells actually contain separated fields, then select Next. Choose General in the column data format section and select Finish. Excel converts number-like text to numeric values, so a text value such as 00123 becomes the number 123. Review the results before saving.
  5. Use the warning icon when Excel offers a conversion. Some text numbers have a small green triangle in the cell. Select one or more of those cells, click the warning icon that appears, and choose Convert to Number. This is a convenient choice for a small range, but the option may not appear for every cell or every type of imported data. If it is missing or you need to convert many cells consistently, use Text to Columns instead.
  6. Use a formula when you want to keep the original column temporarily. If the text values are in A2:A100, enter this in B2: =VALUE(A2). The formula returns a numeric value without the leading zeros; for example, if A2 contains the text 00123, the result is 123. Fill the formula down the helper column by dragging the fill handle or double-clicking it. Check that the results are correct, then copy the formula results and use Home > Paste > Paste Values to replace formulas with fixed values. Keep or delete the original column only after checking the converted results.
  7. Check the output and save a safe copy. Look at several cells from the top, middle, and bottom of the range. Confirm that ordinary numbers have lost only the zeros you intended to remove, and check for errors or changed values. If you converted a column using a helper formula, keep the source column until you are satisfied. Save the workbook under a new name if you may need to restore the original data.

For routine numeric data, Text to Columns is often the fastest in-place option. A helper formula is easier to audit because the source remains beside the result. The number-format method is best when the cell already contains a number and the zeros are only a visual effect.

Common Mistakes

Changing General format and expecting text to convert. General controls how numbers are displayed; it does not strip characters from a text value. If the formula bar still contains 00123 after changing the format, convert the text using Text to Columns, Convert to Number, or a formula.

Removing zeros from identifiers that require them. Excel treats 00123 and 123 as the same numeric value, but a company may treat them as different product codes. Postal codes, membership numbers, invoice references, and account numbers are commonly identifiers rather than quantities. Confirm the rules for the data before converting it.

Converting long digit strings to numbers. Excel preserves only 15 significant digits in numeric values. If an ID has more than 15 digits, conversion can permanently change digits at the end. Keep long identifiers as text, even if you want to remove leading zeros; use a text-cleaning approach that does not convert the entire value to a number, and verify the result against the source.

Overwriting source data too early. A conversion can be difficult to reverse after the workbook is saved or other changes are made. Keep a backup column or duplicate sheet until you have reviewed the results. Also avoid selecting entire adjacent columns by mistake when only one range needs changing.

Assuming every text cell is a simple number. A column may contain blanks, spaces, letters, punctuation, or mixed values. The VALUE formula returns an error for text that Excel cannot interpret as a number, and Text to Columns may interpret date-like entries as dates. Scan the range first, and do not apply a bulk conversion blindly to mixed data.

Tips

  • Use a test copy or a few sample cells first, especially with imported files. Confirm what Excel does before applying the method to thousands of rows.
  • If you need to preserve the original order and exact source values, add a helper column rather than converting in place. You can compare the original and result side by side.
  • For a column that should remain numeric after conversion, choose a suitable number format such as Number or General. Do not use a custom format containing zero placeholders if you want the zeros to stay hidden.
  • If data comes from a CSV or another system, consider whether leading zeros are part of the source identifier. When importing, set identifier columns to Text if Excel must not change them.
  • On Excel for the web, the ribbon and conversion options can differ from desktop Excel. If Text to Columns or a warning-icon conversion is unavailable in your web session, open the workbook in the Windows or Mac desktop app and use the steps above.
  • For repeated cleanup, use a consistent process: preserve the raw import, convert a separate working column, compare the results, and only then replace or export the cleaned values.

Troubleshooting

The zeros are still visible after choosing General. Check the formula bar. If it contains the zeros, the value is text, so a display-format change cannot remove them. Convert the text values instead. If the formula bar shows the number without zeros, inspect Format Cells for a custom number format or check whether conditional formatting is affecting the appearance.

VALUE returns an error. The source may contain spaces, letters, separators, or other characters. Click the original cell and inspect its contents. If the only issue is ordinary spaces before or after the digits, you can try =VALUE(TRIM(A2)) in a helper column; with 00123 in A2, the result is 123. TRIM removes ordinary extra spaces but not every special character, such as some nonbreaking spaces. If the cell contains letters or an identifier, do not force it into a number; use a text-safe cleanup method and retain the result as text.

Text to Columns changes dates or other entries unexpectedly. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if you have not made later changes. Then restore the source range and inspect the column for mixed content. In the Text to Columns wizard, choose Text for a column that must remain text; use General only when you want Excel to interpret values as numbers. Test a copy before processing the full range.

Some cells convert and others do not. The range may mix numbers stored as text with genuine text, blanks, or values containing hidden spaces. Filter or sort the column to inspect unusual entries, and work on a copy. Use formulas only on rows that contain numeric text, and review any errors rather than replacing them automatically.

The converted result has lost digits. If a long identifier was converted to a number, Excel may have exceeded its 15-digit precision limit. Undo if possible and restore the original text from the source or a backup. Store long digit strings as text from the start; changing the cell format afterward cannot recover digits that Excel has already rounded.

Frequently Asked Questions

Can I remove leading zeros without a formula?
Yes. If the zeros are part of text, use Data > Text to Columns and choose General, or use the cell warning icon and select Convert to Number when it is offered. If the zeros come from formatting, choose General in the Number Format list.

Why does Excel keep showing zeros after I change the format?
The zeros may be stored as text, not supplied by the format. Check the formula bar: if it still shows the zeros, convert the text value. If the formula bar shows a number without them, inspect custom formatting.

Will removing leading zeros change my data?
It changes text such as 00123 into the number 123, which may be correct for a quantity but not for an identifier. Numeric values also cannot retain meaningful leading zeros. Keep codes, account references, and similar labels as text when their exact digits matter.

Can I remove zeros from a whole column at once?
Yes. Select the data cells in the column and use Text to Columns, or enter a helper formula and fill it down. Review the selected range first so you do not convert headings, unrelated cells, or identifiers that need their zeros.

How do I keep some leading zeros but remove extra ones?
Decide the required length and rules before changing the values. If the result is an identifier, keep it as text and use a text-based method tailored to that rule; converting to a number removes all leading zeros and may damage long values. Test the rule on sample entries first.

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.