When entering numbers into Google Sheets, leading zeros are removed by default. This can be problematic for ZIP codes, phone numbers, and IDs that you type into a cell. Here’s how you can keep leading zeros.
Keep Leading Zeros as You Type
If you want to keep a leading zero on the fly, you can enter an apostrophe (‘) before you enter the number that begins with zero.
The apostrophe acts as an escape character and tells Sheets to ignore its programming that trims leading zeros.
Press the Enter key, and the number will show in the cell without displaying the apostrophe at the beginning. You can even still use the number in formulas without having to worry about it breaking the calculation because of the extra character.
Apply Plain Text Formatting
If you don’t feel like entering an apostrophe every time you type a number with a leading zero, you can format the cells beforehand as plain text. By applying plain text formatting in advance, you can save time and not worry about a zero disappearing.
First, select the range of cells you want to format as plain text.
Next, click Format > Number and then select “Plain Text” from the list.
Now, anything you enter into the cell will show exactly how you type it in plain text.
Apply a Custom Number Format
Another approach is to apply a custom number format to anything you might enter into a cell. If you have a number that’s precisely five digits long—like a ZIP code—you can format the cells to display only that many numbers.
A couple of caveats to note: If you enter only three numbers, Sheets adds two leading zeros for you; if you enter more than five, Sheets will cut off anything over the specified limit.
For example, entering “9808309” would trim the first two off and display only “08309” because those are the last five digits in the number.
Just as with the previous method, you should apply this format preemptively to avoid any potential zero trimming.
First, select the range of cells you want to format as plain text.
Next, click Format > Number > More formats and click on “Custom Number Format” from the list.
Here, you can enter your own customized way to format numbers. The Google Docs help center has a list of common syntax characters to help you create simple or complex number formats.
For our example, we want to format our entry as a five-digit number. So, type five zeros into the text field provided and then click “Apply” to create the new number format.
Using five zeros will force those cells to have a fixed length regardless of what you enter into them and add an insignificant 0 for each missing digit.
Sheets’ number formatting is a powerful tool that lets you format ZIP codes, phone numbers, and ID numbers that could contain a leading zero without worrying about it getting left out.
