How to remove leading zeros from text in Excel

Q: How do I remove leading zeros and get a number?

For digits only: =VALUE(A2) or =--A2.

Q: How do I add the zeros back?

Pad with TEXT: =TEXT(B2,"000000") for numbers.

Excel formula

=MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))

Start at the first character that isn't 0: =MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2)). It works on codes with letters, where VALUE fails.

Result on the example: one result per row in B2, filled down to B6
Works in: Microsoft 365 only
Google Sheets: Different formula, see below

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

MID(A2,SEQUENCE(LEN(A2)),1)<>"0" checks each character and gives TRUE for anything that isn't a zero. MATCH finds the first TRUE, the position where the real code starts, and MID returns everything from there. Zeros inside the code, as in "1050", are kept.

In Google Sheets, ^0+ matches the run of zeros at the start.

=MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))

Result"123"

A2"000123"
The value in A2.
LEN(A2)6
LEN: Number of characters.
- textA2"000123"
SEQUENCE(LEN(A2)){6 rows × 1 col} 1, 2, 3, 4, …
SEQUENCE: A grid of sequential numbers. Spills.
- rowsLEN(A2)6
MID(A2,SEQUENCE(LEN(A2)),1){6 rows × 1 col} "0", "0", "0", "1", …
MID: Characters from the middle of text.
- textA2"000123"
- start_numSEQUENCE(LEN(A2)){6 rows × 1 col} 1, 2, 3, 4, …
- num_chars1
MID(A2,SEQUENCE(LEN(A2)),1)<>"0"{6 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …
The <> operator tests whether they differ.
MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0)4
MATCH: Position of a value in a list. 0 = exact match.
- lookup_valueTRUE
- lookup_arrayMID(A2,SEQUENCE(LEN(A2)),1)<>"0"{6 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …
- match_type0
LEN(A2)6
LEN: Number of characters.
- textA2"000123"
MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))"123"
MID: Characters from the middle of text.
- textA2"000123"
- start_numMATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0)4
- num_charsLEN(A2)6

In Google Sheets

Google Sheets needs a different formula: =REGEXREPLACE(A2,"^0+",""). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=REGEXREPLACE(A2,"^0+","")Google Sheets	123, 42, AB12…	Google Sheets: delete the zeros at the start with a pattern.

Common mistakes

A code that is all zeros ("0000") has no non-zero character, so MATCH returns #N/A. Wrap the formula: IFERROR(…,"0").
For codes that are only digits, =VALUE(A2) is simpler, but it returns a number, and it fails on "000AB12".

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Remove leading zeros from text

In B2, remove the zeros at the start of the code, keeping the rest as text, and fill down to B6.

Questions

How do I remove leading zeros and get a number?

For digits only: =VALUE(A2) or =--A2.

How do I add the zeros back?

Pad with TEXT: =TEXT(B2,"000000") for numbers.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.