When entering UK postal codes, telephone numbers, bank account sort codes, or inventory SKU barcodes into Google Sheets, the spreadsheet software automatically treats numeric input as standard mathematical integers. As a result, typing a value like 01234 causes Google Sheets to strip away the initial zero, displaying only 1234. For financial records, customer address books, and shipping logistics, losing leading zeros corrupts essential data and causes postal delivery failures or failed phone dialling. Fortunately, Google Sheets provides multiple built-in formatting methods to preserve leading zeros perfectly without breaking your data structure. Here is how to keep leading zeros in Google Sheets for numbers, phone numbers, and postal codes.
Why Does Google Sheets Remove Leading Zeros?
Spreadsheet software defaults to mathematical formatting for all unformatted cells. In mathematics, the number 050 holds the exact same numerical value as 50, so the application automatically trims superfluous zeros to conserve visual space and simplify calculation formulas. However, codes, phone numbers, and postcodes are categorical identifiers rather than mathematical values intended for addition or subtraction. To preserve zeros, you must instruct Google Sheets to treat the cell contents either as plain text or as a custom numeric string with a fixed digit count.
Method 1: Format Cells as Plain Text Before Entering Data (Best for New Lists)
The simplest preventative measure is to pre-format your column or cell range as plain text before typing or pasting numbers:
- Select the column or range of cells by clicking the column letter header (e.g. column B).
- Click on the Format menu in the top toolbar.
- Hover over Number.
- Select Plain text from the drop-down list.
- Type or paste your numbers starting with zero (e.g.
07123456789or02138).
Google Sheets will now preserve every character exactly as typed, aligning the text to the left side of each cell to indicate text status.
Method 2: Use the Single Apostrophe Prefix (Quickest for Individual Cells)
If you only need to enter one or two values with leading zeros into an existing sheet, you can bypass formatting menus entirely by using a single quote mark:
- Click into the target cell.
- Type a single apostrophe (
') immediately followed by your number (for example,'0845123). - Press
Enter.
The apostrophe serves as an escape character telling Google Sheets to treat subsequent characters as literal text. The quote mark itself remains hidden in the sheet view and only appears in the formula bar.
Method 3: Apply Custom Number Formatting (Best for Fixed-Length Codes)
If you have existing numbers whose leading zeros were already stripped (e.g. 5-digit US ZIP codes or 6-digit product SKUs) and you want to restore them automatically without manual retyping:
- Highlight the column containing your truncated numbers.
- Click Format > Number > Custom number format.
- In the text input box at the top, enter a string of zeros matching the exact required length. For example, for a 5-digit postal code, type:
For a 10-digit telephone number or SKU, type:000000000000000 - Click Apply.
Google Sheets will instantly pad every number in that column with leading zeros up to the specified digit count (e.g. 742 automatically becomes 00742), while allowing you to still use the cells in mathematical formulas if needed.
Method 4: Restore Leading Zeros Using the TEXT Formula (Best for Large Datasets)
When preparing data for CSV export or merging fields from another database, formula functions provide dynamic zero-padding:
- Click into an empty adjacent helper column (for instance, cell
C2). - Enter the following formula to format the value in cell
A2to a 5-digit string:=TEXT(A2, "00000") - Press
Enter, then drag the blue fill handle down the column to apply the formula across all rows. - To replace original values with static text, copy the formula results, right-click the original column, and select Paste special > Values only.
Comparison: Leading Zero Preservation Methods in Google Sheets
| Method | Underlying Data Type | Main Use Case | Math Formula Compatibility |
|---|---|---|---|
| Plain Text Format | Text string | Pre-formatting columns for phone numbers & UK postcodes | No (treated as non-numeric text) |
Apostrophe Prefix (') |
Text string | Quick one-off data entry without touching menus | No |
Custom Number Format (00000) |
Numeric integer | Restoring fixed-length SKUs while keeping numeric values | Yes (can still calculate sums & averages) |
| TEXT Function | Text string | Automated batch conversion before CSV export | No |
Applying the appropriate formatting method ensures your contact directories, inventory tables, and customer shipping lists remain accurate and error-free across all exports and imports.