Why does Excel delete the leading 0 from phone numbers?

Question

Weirdo · Accepted Answer

Excel treats the entry as a number, and a leading zero means nothing in a number, so it drops it. Format the cells as Text before you type or paste, and numbers like Taiwan mobile 0912345678 keep their leading 0.

Microsoft's documentation says that when you type numbers that begin with zeros, such as phone numbers, credit card numbers, product codes or postal codes, Excel removes the leading zeros. To Excel, 0912 and 912 are the same number, so the default General format simplifies it.

Before typing or pasting, select the cells, press Ctrl+1 (or right-click and choose Format Cells), choose Text under Number, then enter or paste the numbers. The leading 0 is kept.

Changing the format to Text afterwards does not bring back zeros that were already removed. Use the TEXT function instead: a Taiwan mobile number has 10 digits, so in a helper cell enter =TEXT(A1,"0000000000"), where the number of zeros is the total digits you want, then fill down. Copy and paste as values to keep the result.

Why does Excel delete the leading 0 from phone numbers?

Why the zero disappears

Prevent it: set the cells to Text first

If the zeros are already gone