# How to create an ID with a prefix and leading zeros in Excel

Pad the number with TEXT and join the prefix: =$E$1&TEXT(A2,"00000") turns 42 into INV-00042.

**Excel:** `=$E$1&TEXT(A2,"00000")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Number | Invoice ID |  | Prefix | INV- |
| 1 |  |  |  |  |
| 2 |  |  |  |  |
| 42 |  |  |  |  |
| 317 |  |  |  |  |
| 1205 |  |  |  |  |

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

Result: INV-00001; INV-00002; INV-00042; INV-00317; INV-01205

## How it works

Each 0 in the format `"00000"` is a digit that is always shown, so 42 becomes "00042". Joining the prefix in front gives IDs that are all the same length and sort correctly as text.

## Other ways

- `=$E$1&RIGHT("00000"&A2,5)`: Pads by putting zeros in front and keeping the last 5 characters.
- `=$E$1&TEXT(ROW()-1,"00000")`: Numbers the rows by position instead of from a column. (INV-00001 to INV-00005, one per row, whatever is in column A.)

## Common mistakes

- Numbers with more digits than the format are not cut: 123456 gives "INV-123456", one digit longer than the rest.
- IDs based on ROW() change when you sort or delete rows. Use them only to create IDs, then paste them as values.

## Questions

**How do I generate a whole list of IDs at once?**

In Excel 365: `="INV-"&TEXT(SEQUENCE(100),"00000")` spills 100 IDs.

**How do I get the next ID?**

Pad the highest number plus one: `=$E$1&TEXT(MAX(A2:A6)+1,"00000")`.

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/create-id-with-prefix-and-leading-zeros