Saltar al contenido

How to enter zero before a number in Google Spreadsheets

How to highlight a row in Google Spreadsheets using conditional formatting

When you enter numbers in Google Spreadsheets, the zeros at the top are removed by default. This can be problematic for the zip codes, phone numbers, and IDs that you enter in a cell. This is how you can keep zeros.

Keep zero as you type

If you want to keep a zero moving, you can enter an apostrophe (‘) before entering the number starting with zero.

When writing 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 him Spreadsheets to ignore his schedule, which eliminates the leading zeros.

Press the Enter key and the number will be displayed in the cell without displaying the apostrophe at the beginning. You can still use the number in formulas without worrying that it will not calculate due to the extra character.

While the formula editor has the apostrophe, the cell does not actually display 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 in advance as plain text. By applying the plain text format beforehand, you can save time and not worry about the disappearance of a zero.

First, select the range of cells that you want to format as plain text.

First select all the cells you want to format.

Then click Format> Number, and then select «Plain text» from the list.

Then click Format> Number, and then click «Plain Text.»‘ class = «alignnone wp-image-470475 size-full» height = «151» src = «https://www.howtogeek.com/wp-content/uploads/2020/01/2020-01-03_12h24_44.png» stil = «border: 0px; box-sizing: inherit; height: auto; max-width: 650px;»  width = «650»></p>
<p style=Now, everything you enter in 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 everything you enter in a cell. If you have a number that has exactly five digits, such as a zip code, you can format the cells to display just as many numbers.

A few caveats to remember: if you enter only three numbers, Spreadsheets add two initial zeros; if you enter more than five, Spreadsheets will cut anything that exceeds the specified limit.

For example, entering «9808309» would remove the first two and show only «08309» because these are the last five digits of the number.

As with the previous method, you should apply this preventative format to avoid any potential scratching.

First, select the range of cells that you want to format as plain text.

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

Then click Format> Number> Multiple Formats and click «Custom Number Format» from the list.

Then click Format> Number> Multiple Formats, and then choose «Custom Number Format» from the list.’ class = «alignnone wp-image-470579 size-full» height = «387» src = «https://www.howtogeek.com/wp-content/uploads/2020/01/2020-01-03_13h06_53.png» stil = «border: 0px; box-sizing: inherit; height: auto; max-width: 650px;»  width = «650»></p>
<p style=Here you can enter your own custom number formatting mode. 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. Therefore, enter five zeros in the provided text field, and then click «Apply» to create the new number format.

Enter five zeros in the text field and click "Apply" to create the custom format.

Using five zeros will force these cells to have a fixed length, no matter what you enter in them and add a negligible 0 for each missing digit.

Perfectly formatted postal codes.


Sheet number formatting is a powerful tool that allows you to format zip codes, phone numbers, and identification numbers that might contain a zero at the top without worrying about being left out.

.