There are situations where you will be forced to add leading zeros to your data in Google Sheets. Leading zeros are important when working with Employee IDs especially. You will want your entire data to be the same size. In the case of Google Sheets, even if you try typing in leading zeros to your data, it will not keep them. However, there are many ways that you can use to add leading zeros to your data in Google Sheet.
Using the Format as Text Method
Table of Contents
It is a common method since it involves changing the format of your cells. Changing the format of your cells enables the data to be easily stored with leading zeros. Follow these simple steps;
1. Highlight the cell that you need to change their format
2. Select Format in the menu
3. Click on Number from the choices given. All the number formatting options will open
4. Select on Plain Text option. In this way, the cell formatting is changed to text form.
You will thus be able to type cells with leading zeros, and the system will save them. In this way, the system considers all values in the cells as text since we changed the format.
Using the Custom Format Method
The custom format method is used with spreadsheets in Excel and Google Sheets. When using this method, set the length of your numbers. For example, if you set the custom formatting to show six digits, the system will always try to adjust the length by adding leading zeros. You can use the following steps
1. Add data to the cells. Select the cells
2. Go to Format on the Menu
3. Choose the Number option
4. Go to More formats
5. Click on Custom number format
6. Type the number format as 000000. It is a six-digit number format
7. Click on Apply
The format of the cells will finally be changed, and leading zeros can be kept.
Using the Apostrophe Method
It is considered to be the simplest method of adding leading zeros. All you need to do is insert an apostrophe before typing any number. The number is converted into a text that allows the leading zeros to be kept. For example, if you want to add three leading zeros to the number 139, all you will have to do is insert an apostrophe before the number, as in, ‘000139. The number thus holds the leading zeros in the cell.
Using the TEXT Function Method
This is a dynamic way of adding leading zeros to your numbers in the cell. In this case, the TEXT function takes a cell reference as an argument and then changes the cell’s value to the text. The TEXT function has a syntax that allows you to format numbers as text. This is;
TEXT (number, format)
The number- gives the numeric value
The format- It gives the format in which you want the numeric values to be applied.
