# How to remove leading zeros from text in Excel

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.

**Excel:** `=MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))` (Microsoft 365 only)

**Google Sheets:** `=REGEXREPLACE(A2,"^0+","")`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Code (text) | Without zeros |
| 000123 |  |
| 0042 |  |
| 000AB12 |  |
| 1050 |  |
| 0007-B |  |

Formula in B2, filled to B6: `=MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))`

Result: 123; 42; AB12; 1050; 7-B

## 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.

## Other ways

- `=REGEXREPLACE(A2,"^0+","")`: 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".

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/remove-leading-zeros-from-text