Keeping the leading zero in a phone number or postcode

Type 0612345678 into a cell, and the spreadsheet turns it into 612345678. After all, a number never starts with a zero. But with phone numbers, Belgian postcodes, article codes and customer numbers, you want to keep that zero.

Beforehand: set the column to Text

1. Select the column (click the column letter) before you type or paste anything.
2. On the Home tab, click the number format drop-down list (1) and choose Text.
3. Everything you now type or paste 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 article code always need to be six digits (000123)? At the bottom of the drop-down list, choose More formats and create your own 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 doesn't bring the zero back. In an empty column, enter =TEXT(D2,"0000000000") for ten-digit phone numbers, drag it down, then 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 can perfectly well be text too.
• With a CSV import, the import itself determines whether the zero is kept. If it went wrong, set the column to Text afterwards and import again. See Opening a CSV file with semicolons and strange characters correctly.
• Long numbers such as an IBAN or national ID number should also be text: as a number, they'll be rounded off or shown as 1.23E+17.