The formula
Pad to 5 digitsExcelGoogle Sheets
=TEXT(A2,"00000")In C2 → 00042
Pad (works for text too)ExcelGoogle Sheets
=RIGHT("00000"&A2,5)In C3 → 00042
Remove leading zerosExcelGoogle Sheets
=--A3In C4 → 731
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | |
|---|---|---|---|
| 1 | Value | ||
| 2 | 42 | 00042 | |
| 3 | 00731 | 00042 | |
| 4 | 731 |
How it works
- "00000"
- Format code: 5 digits, zero-padded.
- RIGHT("00000"&A2,5)
- Adds zeros in front, then keeps the last 5 characters.
- --A3
- Converts the text "00731" to the number 731.
Common errors and fixes
Result is text?
TEXT returns text; that's what preserves the zeros. Don't do math on it.
Value longer than 5 digits cut off?
The RIGHT version truncates. Use TEXT for numbers.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.