# How to add leading zeros to numbers in Excel

Format the number as text with a fixed number of digits: =TEXT(A2,"00000").

**Excel:** `=TEXT(A2,"00000")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| ZIP (imported) | ZIP code |
| 2134 |  |
| 10001 |  |
| 6510 |  |
| 94105 |  |
| 501 |  |

Formula in B2, filled to B6: `=TEXT(A2,"00000")`

Result: 02134; 10001; 06510; 94105; 00501

## How it works

Spreadsheets drop leading zeros from numbers because 02134 and 2134 are the same number. TEXT with the pattern `"00000"` pads it to five digits; each 0 in the pattern is a digit that is always shown.

## Other ways

- `=RIGHT("00000"&A2,5)`: Pads by joining zeros in front and keeping the last five characters.

## Common mistakes

- The result is text, which is right for IDs and ZIP codes but won't add up in a SUM.
- To keep the value as a number and only change how it looks, use a custom number format 00000 on the cell.
- For codes that must keep their zeros, format the column as Text before typing or importing.

## Questions

**How do I pad to a variable length?**

Use REPT: `=REPT("0",8-LEN(A2))&A2` pads to 8 characters.

**How do I remove leading zeros?**

Convert to a number: `=VALUE(A2)`.

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

Source: https://spreadsheetpractice.com/formulas/add-leading-zeros