Chapter 15 · 15.6
Keeping the leading zero in a phone number or postcode
Type 0612345678 into a cell, and the spreadsheet turns it into 612345678. A number never starts with a zero, after all. But with phone numbers, Belgian postcodes, item codes and customer numbers, you want to keep that zero.
First: set the column to Text
- Select the column (click the column letter) before you type or paste anything.
- On the Home tab, click the number format drop down list (1) and choose Text.
- Everything you type or paste now stays exactly as it is, including the zero.
Quick fix for a single cell
Type an apostrophe before the number: '0612345678. You won't see the apostrophe in the cell, but the zero stays.
Codes with a fixed length
Does an item code always need to be six digits (000123)? At the bottom of the drop down list, choose More formats and create a custom format 000000. The code then remains a number you can sort by, but it's always padded with zeros.
Has the zero already disappeared?
Setting the format to Text afterwards won't bring the zero back. In an empty column, enter =TEXT(D2,"0000000000") for ten digit phone numbers, copy it down, and paste the result back as values (right click, Paste special).
Pitfalls
- A phone number isn't a number you calculate with. So +31 6 12345678 with spaces works perfectly well as text too.
- With a CSV import, the import itself decides whether the zero is kept. If it goes wrong, set the column to Text afterwards and import again. See Opening CSV files with semicolons and strange characters correctly.
- Long numbers such as an IBAN or national ID number should also be text: as a number they get rounded or shown as 1.23E+17.