Excel Leading Zeros
As we know that Excel is mostly used to keep data in rows and columns whether it will be numerical or character. We can easily able to store numerical data like “123” and perform calculations on them. But what about the numbers which are starting from “0” like Zip_Codes, Security_No. , Employee_ID, Excel will automatically trim those numbers removing ”0” from them.
Suppose there is a Zip_Code starting with “001495”. If we write this in a cell, Excel will instantly shorten to “ 1459”. There are different ways in which we can keep zeros in Excel data.
From the above example, we can observe that “0” is removed and the remaining number is “1459”.
The reason behind removing zeros is that Microsoft Excel considers Zip_Codes, Employee_ID, and other numerical data as numbers, and pertains the General format to them, and removes the Leading Zeros.
Excel also provides a way to keep these zeros in cells. In further in this tutorial, we will see how this can be done.
How to Add Leading Zeros in Excel?
Employee_ID, and Name. While entering the name we will not face any difficulty because name always starts with characters, but Zip_Codes and Employee_ID can be a combination of character or number and starts with “042067”, “0021WNS”. If we enter these values in the cells the cells will truncate the values to“42067”, “21WNS”.
To keep this value as it changes the format of cells:
- Select the cell where you want to enter the value. In this case, we have B2
- Go to Home tab -> Number group -> select Text in the Format box instead of General.
After changing the format of the cell, insert the value.
To keep the same format in all the cells copy and paste the format of these cells to other cells using Format Painter.
- Select the cell whose format to be copied. Click on Format Painter under Home. Select and drag the cells to paste their format.
Now if we type values in these cells with leading zeros will not trim the “0”.
#1 – Adding Leading Zeros Using Excel TEXT Function
Value:- It contains the cell to be modified. In this case, we have C3.
Formatted_text:- It contains the value to be added i.e. “0”.
We can also add leading zeros using TEXT Function. This function pads numbers with “0” by manually inserting 0’s in cells.
To add 0’s with the TEXT function
Step 1 – Select the column and write the TEXT formula.
Step 2 – Select the C3 in the value and to add leading zero types ”00000” five zeros in formatted_text because there is a 4-character string.
Step 3 – Close the round parenthesis and press Enter the result is shown below.
NOTE:- TEXT Function will always return value I form of the string not in numbers.
#2 – Adding Leading Zeros Using Excel RIGHT Function
We can also use the RIGHT function to insert Leading Zeros in a string.
We can use this by using the following syntax:
TEXT – Will include the text to be inserted.
Num_chars – Total number of characters in the string to be displayed.
We can insert a maximum of 4 zeros “0000”. If you want to insert “00” in front of the “3240” to make it “003240” as given in the above example we have to write a formula.
=RIGHT(“000000” &B3, 6 )
We are writing 6 because we want the total number of 6 character string in a cell.
B3 is a reference to a cell containing the original value.
What the formula does that it adds the 6 zeros to the text in B3 (“000000” &B3), and then selects 6 characters from right and displays in the selected cell.
Steps to Add Leading Zeros in Excel using the RIGHT Function:
- Select the cell to insert the value.
- Write the formula with the syntax:
=RIGHT(“000000” &B3, 6)
- Press Enter key to apply the formula the result is shown below.
- To apply formula just drag the cell to bottom and leave the mouse.
NOTE: If we apply the same formula on other cells which don’t have value, it will return 6 zeros.
#3 – Adding Leading Zeros Using Excel CONCATENATE Function
We can also add leading zeros using CONCATENATE Function.
Text_1: Text to be inserted
Text_2: Original text or source text.
For example: If you want to insert 2 zeros write = CONCATENATE(“00”, Text_2)
Steps to insert leading zeros using CONCATENATE Function:
Select the cell in which you want to insert the value and write the concatenate function:
Press the Enter key to apply the formula and the result is shown below.
To apply formula just drag the cell to bottom and leave the mouse.
NOTE:- In this CONCATENATE function we can insert as many zeros as we can in front of the text to the cells in columns i.e. 2 leading zeros (00), 3 leading zeros (000). Regardless of how many characters original value contains.
Things to Remember
- It is any zero digits that come before any string.
- It can be used in different ways like Zip_Codes, Security_No. , Employee_ID.
- It can be used for Error prevention.
- It does not work with negative numbers like, if we write -0024, it will remain the same and not truncate the value to -24.
This has been a guide to Leading Zeros in Excel. Here we discuss how to add the Leading Zeros in Excel using 1) Right Function 2) & TEXT Function 3) Concatenate Function along with practical examples and downloadable excel template. You may learn more about excel from the following articles –