How to remove leading zeros in Excel

First check what you have

Leading zeros only survive in two ways, and the fix depends on which one you have. Either the cell is text (it sits on the left, often with a green triangle, and =ISTEXT(A2) is TRUE), or it is a real number with a custom format such as 000000 that adds the zeros on screen.

For the second kind, nothing needs converting: select the cells, press Ctrl+1 and set the format back to General. The zeros vanish because they were never in the value. Everything below is about the first kind, text, which is what you get from warehouse systems, ERPs and CSV exports opened as text.

Convert to a number: VALUE or --

When the column is a quantity or a numeric ID you will calculate with, turn it into a number. VALUE does it, and two minus signs in front do the same in fewer keystrokes. A number cannot hold leading zeros, so they simply go. VALUE also ignores leading and trailing spaces, so " 00310" becomes 310.

Text quantities like column C are why a SUM of the column returns 0. Convert inside the total with SUMPRODUCT(--C2:C7), or convert the column once and sum normally.

Keep it text: strip zeros from codes

Part codes like 0071-B and 00A-1043 contain letters or dashes, so VALUE returns #VALUE!. You want the same code without the zeros at the front, still as text. The formula finds the position of the first character that is not a zero and returns everything from there on.

How it works: MID(B3,SEQUENCE(LEN(B3)),1) splits the code into single characters, <>"0" marks the ones that are not zero, MATCH(TRUE,...,0) finds the first of them, and the outer MID keeps the rest. SEQUENCE needs Excel 365 or 2021. Zeros in the middle or at the end are untouched, so 04450 becomes 4450, not 445.

Codes that are all zeros, and IDs over 15 digits

A code of 0000 has no non-zero character, so MATCH finds nothing and returns #N/A. Wrap the formula in IFERROR and decide what an all-zero code should become, usually "0".

Long reference numbers are the other reason to keep the text method. Excel keeps only 15 significant digits in a number, so =VALUE(D2) on the 19-digit tracking ref 0004098812345678901 turns the last digits into zeros. The MID formula never converts to a number, so every digit survives.

Don't use SUBSTITUTE to remove zeros

It is tempting to replace "0" with nothing, and on 0012 it looks like it works. But SUBSTITUTE removes every zero, not just the leading ones, so any code with a zero in the middle or at the end is silently changed. The same goes for Find and Replace with 0 in Find what.

No formula: Convert to Number or Text to Columns

To fix the column in place, use one of Excel's built-in conversions. Both turn numeric text into numbers, which drops the zeros. Run them only on columns that are purely numeric: on mixed columns, Text to Columns with General format can turn a code that looks like a date, such as 04-11, into a date.

If you need the opposite, padding 482 back to 000482, see the add leading zeros guide.

1. Quick way: select the cells, click the yellow warning icon next to the selection and choose Convert to Number.
2. For a whole column: select it and go to Data > Text to Columns.
3. Choose Delimited and click Next, clear every delimiter box and click Next.
4. Under Column data format, keep General and click Finish. Text like 000482 becomes the number 482.