Text Functions
Advanced

REPT to pad a number with zeros

Make every ID the same width: as many zeros as are missing, then the number.

Task:

The new system needs six-digit employee IDs with leading zeros, so 42 becomes 000042. Build the padded ID in B2:B6.

Interactive Spreadsheet

Read about REPT

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of REPT

0/3

The same formula in the other shapes it takes at work.

  1. REPT Function

    Repeat text a specified number of times.

    Core
    Intermediate
  2. REPT with a scaled bar

    Big numbers need dividing down before they become a bar.

    Intermediate
  3. REPT to pad a number with zeros

    Make every ID the same width: as many zeros as are missing, then the number.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 2 columns5 cells you fill in
AB
1Old IDNew ID
242
37
41305
598012
6500
What this exercise teachesMay contain the answer

The result is text, which is what an ID should be — nobody adds employee numbers together, and a number cell would strip the leading zeros straight back off. The padding adapts to each row because the zero count is worked out, not typed.