2 Easy Ways To Add Zero Before a Number in Google Sheets
By default, Google Sheets automatically omits any zeros at the start of a number as it only considers numbers with a value. However, not always you would want to enter numbers with a value in the spreadsheet. For example, zip codes, phone numbers and security numbers, etc. can start with zero.
If you are looking to create a Google spreadsheet with numbers starting with zero, then I can help. In this post, I’ll show you two ways to add a zero before a number in Google spreadsheets.
Method #1: Add an apostrophe in the start
If you don’t need to add a ton of data starting with zero, then this simple trick should solve your problem without needing to change document formatting. All you need to do is add an apostrophe sign (‘) at the start of the number. On most keyboards, the apostrophe (‘) key is found near the “Enter” key.
Once added, move to the next cell and you’ll see that the spreadsheet will not show the apostrophe sign and show the number in full along with zeros. However, when you will edit that cell again, the apostrophe will become visible.
The apostrophe basically tells the cell that data next to it is plain text, so it doesn’t automatically omit zeros from it. When you need to present data, then this trick should work fine. Although for editing a spreadsheet and collaborating with other people, it can create confusion.
Method #2: Change text formatting
If the amount of data is very large that starts with a zero or you simply don’t want an apostrophe in the cell, then you can also change text formatting to plain to achieve the same results. Here’s how:
Click and hold on a cell and then drag to highlight all the cells in which you would be entering the data.
Afterward, click on the “Format” menu and select “Plain text” from the “Number” option.
Now all the data entered in the selected cells will be in plain text and the zeros in the start will not be automatically omitted.
I know it’s quite irritating when the spreadsheet automatically starts removing zeros from the start. However, it’s not spreadsheet’s fault as it has been designed to enter data which usually has some value. I am sure the above two solutions will be sufficient to make sure the zeros are not omitted automatically from the start of a number. If you have any further questions, let us know in the comments below.
if you are going to paste more than the starting number of rows into a sheet (1000), the additional rows will keep the formatting of Number! add the rows you need before pasting.