Formula guide · Excel and Google Sheets · Updated May 13, 2026
Keep Leading Zeroes in Excel or Google Sheets
Preserve leading zeroes in ZIP codes, IDs, SKU codes, and imported text values.
Quick Answer
Format IDs as text before entry, or use TEXT to display numbers with leading zeroes.
Copyable Formula
=TEXT(A2,"00000")Syntax
=TEXT(A2,"00000")Excel and Google Sheets
Worked Example
| A | B |
|---|---|
| Raw number | Code |
| 123 | 00123 |
Result: Displays the value with leading zeroes while keeping a consistent code length.
Steps
- Decide whether the value is a code or a number.
- Use text format for IDs that should not be calculated.
- Use TEXT with a zero pattern when converting numeric values.
- Check imported CSV files before saving over them.
Common Mistakes
- Treating ZIP codes or IDs as numeric values.
- Applying formatting after the leading zeroes were already removed.
- Using TEXT output in numeric calculations by accident.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.