How To Add Leading Zero In Excel Examples With Excel Functions

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. So we can easily store numerical data like “123” and perform calculations on them. But what about the numbers starting from “0” like Zip_Codes, Security_No., Employee_ID, Excel will automatically trim those numbers removing ”0” from them.

Suppose a Zip_Code is starting with “001495”. If we write this in a cell, Excel will instantly shorten it 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.”

Removing zeros is that Microsoft Excel considers Zip_Codes, Employee_ID, and other numerical data as numbers that pertain to the “General” format and extracts the leading zeros.

Excel also provides a way to keep these zeros in cells. In this tutorial, we will see how we can do this.

How to Add Leading Zeros in Excel?

Employee_ID, and Name. While entering the name, we will not face any difficulty because the name always starts with characters, but Zip_Codes and Employee_ID can be a combination of characters or numbers and starts with “042067”, “0021WNS”. Therefore, if we enter these values in the cells, the cells will truncate the values to“42067”, and “21WNS.”

#1 – Adding Leading Zeros Using 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 the TEXT Formula in excelUsing TEXT Formula In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more. This function pads numbers with “0” by manually inserting 0’s in cells.

First, select the cell where we want to insert the value. In this case, we have a B2 cell. Go to the “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 in excel. Select the cell whose format is to be copied. Click on “Format Painter” under the “Home” tab. Select and drag the cells to paste their format. If we type values in these cells with leading zeros, we will not trim the “0”.

To keep the same format in all the cells, copy and paste the format of these cells to other cells using Format Painter in excel.

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 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 the “Enter” key. The result is below.

NOTE:- The TEXT function will always return the value in the form of the string, not in numbers.

#2 – Adding Leading Zeros Using RIGHT Function

We can also use the RIGHT function in excelUse The RIGHT Function In ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more 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 we 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 the 6-character strings in a cell.

B3 is a reference to a cell containing the original value.

What the formula does is that it adds the 6 zeros to the text in B3 (“000000” &B3) and then selects 6 characters from the right and displays them in the selected cell.

Steps to Add Leading Zeros using the RIGHT Function:

Select the cell to insert the value.
Write the formula with the syntax:

=RIGHT(“000000” &B3, 6)

Press the “Enter” key to apply the formula. The result is shown below.
Just drag the cell to the bottom and leave the mouse to apply the formula.

NOTE: If we apply the same formula to other cells which do not have value, it will return 6 zeros.

#3 – Adding Leading Zeros Using CONCATENATE Function

We can also add leading zeros using the CONCATENATE function in Excel.

Text_1: Text to be insertedText_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 we want to insert the value and write the concatenate function:

=CONCATENATE(“00”, B2)

Press the “Enter” key to apply the formula, and the result is shown below.

NOTE:- In this CONCATENATE function, we can insert as many zeros as we can in front of the text to the cells in columns, 2 leading zeros (00), 3 leading zeros (000). Regardless of how many characters the original value contains.

Things to Remember

It is any zero digits that come before any string.We can use it in Zip_Codes, Security_No., and Employee_ID.We can use it for error prevention.It does not work with negative numbers. If we write -0024, it will remain the same and not truncate the value to -24.

Excel Leading Zeros#

How to Add Leading Zeros in Excel?#

#1 – Adding Leading Zeros Using TEXT Function#

#2 – Adding Leading Zeros Using RIGHT Function#

#3 – Adding Leading Zeros Using CONCATENATE Function#

Things to Remember#

Recommended Articles#