Leading Zeros In Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Are Leading Zeroes In Excel?

Leading Zeros in Excel is a method that trims zeros automatically. As we know Excel is mostly used to keep data in rows and columns, whether it will be numbers or alphabets. 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.

leading zeros

From the above example, we can see that the leading zeroes are removed.

Explanation And Uses

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.

This function is used to avoid errors and to read the data with ease. Excel also provides a way to keep these zeros in cells. In this tutorial, we will see how we can do this.

Table of contents
  • Leading zeros in Excel function helps users trim the zeros in the cells automatically. It helps users read the data more effectively.
  • This function is mostly useful when we have Zip_Codes, Security_No., and/or Employee_ID in our data.
  • Remember, Excel’s leading zeros function does not work with negative numbers.
  • For example, if we write -0024, the value will remain the same and leading zeros function does not truncate the value to -24.
  • Also, we can use this function to avoid errors.

How To Add Leading Zeros In Excel?

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.”

To keep this value as it changes the format of cells:

  1. First, select the cell where we want to insert the value. In this case, we have a B2 cell.


    leading zeros 1

  2. Next, go to the "Home" tab → "Number" group → select "Text" in the "Format" box instead of "General."


    leading zeros 1-2

  3. Next, after changing the format of the cell, insert the value.


    leading zeros 1-3

    leading zeros 1-4

    Now, 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.

  4. Next, select the cell whose format is to be copied. Click on "Format Painter" under the "Home" tab. Then, select and drag the cells to paste their format.


    Leading zeros 1-6

  5. If we type values in these cells with leading zeros, we will not trim the "0".


    leading zeros 1-7
    Similarly, we can use leading zeroes function in Excel.

Examples

Example #1 - Adding Leading Zeros Using TEXT Function

laeding 13
  • value:- It contains the cell to be modified. In this case, we have C3.
  • format_text:- It contains the value to be added, i.e., “0”.

We can also add leading zeros using the TEXT Formula in excel. This function pads numbers with “0” by manually inserting 0’s in cells.

To add 0’s with the TEXT function:

Step 1 - To begin with, select the column and write the TEXT formula.

leading zeros 1-8

Step 2 - Next, select the C3 in the value and add leading zero types ”00000” five zeros in format_text because there is a 4-character string.

leading zeros 1-9

Step 3 - Then, close the round parenthesis and press the “Enter” key. The result is below.

leading zeros 1-11

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

Likewise, we can use leading zeroes function in Excel.

Example #2 - Adding Leading Zeros Using RIGHT Function

We can also use the RIGHT function in excel to insert leading zeros in a string.

The syntax is

Right function
  • 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:

  • First, select the cell to insert the value.
leading zeros 1-12
  • Now, write the formula with the syntax:

=RIGHT(“000000” &B3, 6)

leading zeros 1-13
  • Next, press the “Enter” key to apply the formula. The result is shown below.
leading zeros 1-14
  • Then, just drag the cell to the bottom and leave the mouse to apply the formula.
leading 0-1

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

leading 0-2

Similarly, we can use leading zeroes function in Excel.

Example #3 - Adding Leading Zeros Using CONCATENATE Function

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

leading 0-3
  • text1: Text to be inserted
  • text2: 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:

  • To start with, select the cell in which we want to insert the value and write the concatenate function:

=CONCATENATE(“00”, B2)

leading 0-4
  • Next, press the “Enter” key to apply the formula.
leading 0-5
  • Then, just drag the cell to the bottom and leave the mouse to apply the formula.

leading 10

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.

leading 11

Likewise, we can use leading zeroes function in Excel.

Important Things To Note

  • Leading zeros in Excel function trims the zeros in the cells automatically.
  • In simple words, it trims any zero digits that come before any string.
  • It is especially used to trim zeros in zip codes or employee IDs.

Frequently Asked Questions (FAQs)

1. What is the use of leading zeros in Excel function?

Leading zeros in Excel is used to trim the zeros appearing before the string automatically. It is mostly used to avoid errors.
This function is mostly used with data including zip codes and employee IDs.

2. Explain how to use leading zeros function with an example.

Consider the below example showing the stock number of product A. Now, let us learn how to use leading zeros in Excel.

Leading Zeros in Excel - FAQ 2 - 1

Now, we can see the leading zeros are trimmed/removed automatically.

FAQ 2 - 1

Likewise, we can use leading zeros in Excel.

3. What are the formulas which we can use to add leading zeros in Excel?

Leading zeros in Excel is an automatic function and it trims zeros before the string instantly. However, we can use the below functions to insert leading zeros in a string. 
• RIGHT function: =RIGHT(text,)
• TEXT function: =TEXT(value,format_text)
• CONCATENATE function: =CONCATENATE(text1,,…)