We are all aware of how great and dynamic the Google Sheets operates when performing different tasks with a given data type.
But when working on numerical values, Google sheets default settings detect leading zeros in a cell and automatically remove them even though you don’t want them removed.
The major reason why Google Sheets removes leading zeros is that in a number string, zeros are often considered irrelevant and redundant digits when placed at the start of a number string.
This tends to become frustrating, especially when working with large numerical datasets containing bar codes, identification numbers and phone numbers.
In this how-to guide, we are going to learn the methods and functions of How To Stop Google Sheets From Deleting Leading Zeros.
Adding An Apostrophe (‘) before the Number.
This method is quite self-explanatory. As the subtopic says, it is done by simply adding an apostrophe at the start of the number string in the cell. This is done manually and is most
suitable when dealing with small datasets. In the sample worksheet below, we have a number string, 000456 in cell B2. To prevent the removal of the leading zeros, we would add an apostrophe in front of the number string.
That is ‘000456. Press the Enter key. The leading zeros are retained in the number string but the apostrophe is removed.
This is because Google Sheets sees the zeros as texts now. Note that the formatted number can still be used in calculations with functions and formulas. The number remains unaffected still.
Modifying the Format of the Cell To Text.
This is another method used in stopping Google Sheets from deleting starting zeros. It is done by changing the cell’s format to text. This makes any inputted data analyzed as a text.
This method is very easy as many number strings can be inputted into the cells without any leading zeros being removed and it is done in a matter of seconds.
Step 1: For an easier and stress-free task, you can format the entire empty worksheet before entering the numbers. Click on the grey box at the top left corner of the worksheet. This selects all the cells and columns in the worksheet.
Step 2: Navigate your way to the toolbar and click on Format.
Step 3: From the drop-down menu provided, select Number.
Step 4: From the list of additional options, select Plain Text.
Now, if a number string with leading zeros is entered in the text formatted cells, the zeros won’t be removed. Calculations can also be done with the numbers in the cells.
Note that if you copy and paste numbers
with leading zeros in the formatted cells, ensure the numbers are pasted as values. This is done to make sure the text formatting remains unaffected.
Creating A Custom Format for Cells.
The method is somewhat technical from the other methods. Here, a custom format is created where you compel a cell to display certain digits. If you want the cells to show the five numbers
only in a number string, and in a case where the numbers are less than the required amount, a zero is placed at the start of the number string. For example, number string 4456 would be automatically displayed as 04456.
This is done by following the steps stated below.
Step 1: Select the cells or range of cells to which you want their formats changed.
Step 2: Go to the toolbar and click on Format.
Step 3: From the drop-down menu provided, select Number.
Step 4: Select Custom Number Format.
Step 5: In the tab opened, enter the custom format as 0000000 (seven zeros) in the textbox provided.
Step 6: Click on Apply here.
In the selected cell range, any number value inputted would show you the seven digits only and if you enter less than seven numbers, leading zeros would be placed at the start of the numbers.
Note that text strings are unaffected by this custom format.
Using the TEXT Function To Add Leading Zeroes.
There are also functions used to add leading zeros to your numbers. These functions serve as a more dynamic and versatile way of performing this task.
Firstly is the TEXT function. This function takes a value in a selected cell or cell range and modifies the value to a text. The TEXT function takes the syntax below.
=TEXT(number, format)
Where;
- The format is the selected format applied to the selected number or cell reference.
Using the screenshot of the sample worksheet below, we have a range of student ID numbers from cell C2 to C10. We want to add five leading zeros to each ID number.
- The number is the cell reference containing the selected number.
Step 1: In the result column D, we input the formula as =TEXT(C2, “00000”). The added format allows the number length of five digits and if it’s less than five, leading zeros are added at the front of the number string.
Step 2: Press the Enter key. A leading zero is added before the number in cell D2. Also, the number string aligns on the left side of the cell, which means it’s a text or has been converted to a text.
Note that if the value of a cell aligns to the right side of a cell, it is a numeric value.
Step 3: Highlight the cells and drag down the formula, to copy and paste the respective results in their cells or use the suggested autofill to fill in the leading zeros in each ID number in column C.
Now, the TEXT function has added leading zeros to the identification numbers. Note that you can use the specific number instead of the cell reference in the TEXT formula. Example, =TEXT(3356, “00000”). This also produces the same results in cell D2.
Using The RIGHT Function to Add Leading Zeroes.
This method is quite similar and dynamic to the TEXT function. The RIGHT function also allows you to add leading zeros to text strings too. This function allows you to remove several characters from the right side of the string. The RIGHT function takes the syntax below.
=RIGHT( string, [count])
Where;
- The string is a required parameter which represents the entire text or cell reference.
- The count is not a compulsory parameter. It is the number of characters you want to be pulled from the right side of the text or number string.
In a worksheet where there are both texts and numbers, we are going to show you how the RIGHT function works.
Step 1: In cell B1, enter the formula =RIGHT(“0000” & A1, 4).
Step 2: Press the Enter key and two leading zeros are placed before the number ten. The RIGHT function takes four zeros and the text value as the string input.
Then, the count parameter, 4, pulls out four characters from the right side to return a string with two zeros in front of 12. Note that if the formula is used in an empty cell, the formula returns a text with all four zeros.
Step 3: Highlight the cells and drag down the formula, to copy and paste the respective results in their cells or use the suggested autofill to fill in the leading zeros in each ID number in column C.
Using The CONCATENATE Function to Add Locked Number of Leading Zeros.
The CONCATENATE function joins multiple text strings into one and is also used to add a specific number of leading zeros to the cell value. The function takes the syntax below.
=CONCATENATE( string1, [string 2,…])
Where;
- String1 is the first required string in the formula.
- String2 is an optional parameter. It is an additional text string joined with string1.
For instance, in column C, there is an ID numbers from C2 to C10.
We want to join a fixed leading zero to the number, so we type the formula as =CONCATENATE( “00”, C2). With 00 and C5 as string1 and 2 respectively.
Press the Enter key and the result is displayed below.
Highlight the cells and drag down the formula, to copy and paste the respective results in their cells or use the suggested autofill to fill in the leading zeros in each ID number in column D.
Using the BASE Function to Add Leading Zeros.
The BASE function converts a selected numeric value to a base form. It could be binary, denary, octal etcetera. It is a mathematical function and is also used in Trigonometry. The function takes the syntax below.
=BASE(value, base, [min_length]).
Where;
- Value is the mandatory parameter which selects a number that you want to convert to a particular number base.
- The base is the mandatory parameter which determines the number base you want your value converted to. The base is set between 2 and 36.
- Min_length is an optional parameter that specifies the length of the result value.
For example, In cell B2 has some digits 1101.
So in cell C2, we type in the formula as =BASE(B2, 10, 7). where each value in the formula is the value, base and min_length respectively.
Press the Enter key. The function modifies the digits to a string in the denary system and adds leading zeros to adjust the length of the value.
Using the QUERY Function To Add Leading Zeros.
The QUERY function is very dynamic and advanced and is prominently used in Google Sheets. The FORMAT function works in combination with the QUERY function to apply a format to add
leading zeros. The combination formula takes the syntax below.
=QUERY(cell range, ” SELECT * FORMAT A ‘leading zeros'”)
The formula takes the selected cell or cells as the first parameter and leading zeros get added to the values of the cells. The SELECT statement returns the results of the selected cells.
For example, we have digits from cell A2 to A5.
In cell B2, we entered the formula as =QUERY(A2:A5, “SELECT * FORMAT A ‘000000’”)
Press the Enter key and the formula automatically fills up cells B2 to B5 with the results.
Final Thoughts.
This guide has all the detailed methods and functions used in adding leading zeros and stopping the removal of starting zeros in a cell. Now you know How To Stop Google Sheets From Deleting Leading Zeros.
Do you have a favorite method? Do you know any other method apart from the ones stated above?. Thanks for reading.
