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.

When typing in a number that has a leading zero, place an apostrophe (') at the beginning of the number.

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.

While the formula editor has the apostrophe, the cell doesn't actually show it.

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.

Select all the cells where you want to apply the formatting to first.

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.

Select all the cells where you want to apply a custom number format to first.

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.

Perfectly formatted ZIP codes.

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.

Profile Photo for Brady Gavin Brady Gavin
Brady Gavin has been immersed in technology for 15 years and has written over 150 detailed tutorials and explainers. He's covered everything from Windows 10 registry hacks to Chrome browser tips. Brady has a diploma in Computer Science from Camosun College in Victoria, BC.  
Read Full Bio »