Data Cleaning: Pad An ID With Leading Zeros To A Fixed Width

Excel Formulas › Data Cleaning

Imports often strip leading zeros, leaving IDs of different lengths. REPT repeats a zero as many times as the ID is short of the target width, and the ampersand joins the zeros to the ID, so every value reaches the same length.

Functions used (tap for the full reference guide):

The example

Three worked examples. Change any yellow input in the practice workbook and the results recalculate.

The formula

Here is the formula, built directly from the inputs:

How it works

Here is the logic, one piece at a time:

1. LEN(B2) counts the existing characters.
2. C2-LEN(B2) is the missing digits.
3. REPT("0",...) builds the zeros.
4. &B2 appends the ID.

Keep every number in its own input cell so the formula never needs editing.

Try it: interactive demo

Variations

Using TEXT

Fixed width of 8.

Trim spaces first

Clean stray spaces.

Pitfalls & errors

Practice workbook

Frequently asked questions

Related formulas: Standardize Phone Number Format · Clean Currency Text to a Number · Remove Non-Breaking Spaces (TRIM Won't)

Function references: REPTLEN