Text
Add leading zeros to numbers: pad ZIP codes and IDs to fixed length
Pad numeric IDs with leading zeros to a target character width using REPT and MAX(0, ...) so longer codes are never truncated.
Why REPT + MAX(0, ...) is safer than RIGHT("00000" & A2, 5)
Spreadsheets strip leading zeros from numeric entries (00042 becomes 42), breaking 5-digit postal codes and fixed-width ERP keys.
While RIGHT("00000" & A2, 5) works for 1-to-5-digit inputs, it silently chops 6-digit ID 123456 down to "23456"! Computing REPT("0", MAX(0, Width - LEN(A2))) & A2 prepends zeros when needed and leaves longer IDs intact.
Use nonnegative integer IDs or digit-only text and a modest positive integer target width. Store long IDs as text before import: padding cannot restore digits already lost to numeric precision. An empty input needs an explicit blank guard if it should stay empty. The result is text, not a numeric quantity.
| RawId (A) | TargetWidth (B) | RIGHT Shortcut | Safe REPT + MAX |
|---|---|---|---|
| 42 | 5 | 00042 | 00042 |
| 7 | 5 | 00007 | 00007 |
| 123456 | 5 | 23456 (truncated!) | 123456 |
Truncation-safe zero-padding formula
MAX(0, B2 - LEN(A2)) clamps negative pad counts to 0 so REPT never throws #VALUE! when an ID already exceeds TargetWidth.
=REPT("0", MAX(0, B2 - LEN(A2))) & A2Compare overflow behavior on 6-digit inputs
On RawId = 123456 with width 5, REPT + MAX preserves "123456", whereas RIGHT("00000" & A4, 5) drops the leading 1.
=RIGHT("00000" & A4, 5)Try the example
Use the original values or change them to test your understanding.