How to Add Leading Zeros in Excel (4 Quick Methods)

TEXT Functions in Excel

While working in Excel, we often have to add leading zeros before one or more numbers. This is in fact quite easy to implement. Today I will be showing all possible ways how you can achieve that.


Download Practice Workbook


How to Add Leading Zeros in Excel


Let us have a look at this data set. We have the Employee Record of a Company named Star Group.

There are Employee Names in Column B, and their IDs in column C.

A data set in Excel

We see the IDs of each employee consist of three digits. But we want to add leading zeros in front of them to make them six digits.

How can we do that?

You can use the following methods.


1.  Use Excel Toolbar Options to Add Leading Zeros in Excel

You can use Excel Toolbar options to insert leading zeros before any number. You can do this in two formats. Text Format and Custom Format.


Case 1: Text Format

  • Select the range of cells where you want to insert leading zeros. Here I select cells C4 to C12.
  • Go to Home>General option in Excel Toolbar under Number Section.

Number Format options in Excel Toolbar

  • Click on the drop-down menu. You will get these options. Scroll down and click on the last option, Text.

Number Format option in Excel Toolbar

  • You will get the numbers aligned left. That means they are converted to texts. Now double click on them and insert the required number of leading zeros in the front.

Leading Zeros in front of number in Excel

  • You see a small Error Box on the left of the first cell. Click on it. From the options available, click on Ignore Error.

Error Box in Excel

  • Do it for all the cells. Now you have inserted leading zeros before all the numbers.

Note: Now they are in Text Format, so you can not use them in any calculation. If you want to use them in any calculation, change their format to Number from Excel Toolbar.


Case 2: Custom Format

  • Select the cells and again go to Home>General option from under Number Section in Excel Toolbar (See Section 1.1)
  • This time click on More Number Formats.

Custom Format

  • You will get a dialogue box called Format Cells. In the Category option, select Custom. And in the Type option, write “000000”

Format Cells Dialogue Box in Excel

  • Then click OK. You will get leading zeros inserted before the selected the contents of the selected cells, like this.

Leading Zeros in Excel

Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)


2. Use Leading Apostrophe(‘) to Add Leading Zeros in Excel

You can use Apostrophe (‘) in front of a number to convert it to Text Format. Then insert the required number of leading zeros in the front.

Apostrophe before numbers in Excel

Like section 1.1, you will get an Error box on the left. You have to select Ignore Error from there.

This is a much better method for small and simple data set.

Related Content: Excel Number Stored As Text [4 Fixes]


Similar Readings


3. Use Excel’s Functions to Add Leading Zeros in Excel


Method 1: Using TEXT() Function

The syntax of TEXT() function is

=TEXT(value,format_text)
  • Here value is the number which you want to make a text.
  • And format_text is the format in which you want to have your text.

If you want to know more about the TEXT() function visit this link.

Here, we want C4 to be 000111. So our formula will be

=TEXT(111,“000000”)

TEXT Function in Excel

Then we drag the Fill Handle to do the same with the rest of the cells.

drag the Fill Handle to do the same with the rest of the cells


Method 2: Using RIGHT() and CONCATENATE() Function

The Syntax of CONCATENATE() function

=CONCATENATE(text1,text2,...)
  • It takes two or more texts as input.
  • And returns their joint text as output.

If you want to know more about CONCATENATE() Function, visit this link.

As we want to join “000” with C4 our formula will be

=CONCATENATE("000",C4) 

CONCATENATE Function in Excel

Now we drag the Fill Handle to fill the rest of the cells.

CONCATENATE Functions in Excel

We can also do the same operation by using Ampersand Symbol (&). Ampersand Symbol (&) does the same function as CONCATENATE().

The formula then will be:

="000"&C4

Ampersand Formula in Excel

Obviously, if you have four digits in an ID, and you want to make it to 6 digits, you have to add “00” in place of “0000”.

We can do it by combining it with RIGHT() Function.

The syntax for RIGHT() function is

=RIGHT(Text,num_char)
  • It takes a text and a number as input.
  • And returns the text with the specified number of characters from the right as output.

If you want to know more about the RIGHT() Function, visit this link.

So, if we use more than three zeros in the CONCATENATE() Function, we can use the RIGHT() Function to bring it back to three.

The formula will be:

=RIGHT(CONCATENATE("00000",C4),6)

RIGHT Function in Excel

Or you can also use:

=RIGHT("00000"&C4,6)

Method 3: Using Base() Function

The syntax for BASE() function is

=BASE(Value,Base,Number_of_digits)

It takes a value and the base value of the number system as input and returns it in text format with the specified number of digits.

If you want to know more about the BASE() Function, visit this link.

As we want cell C4 to have three zeros before it, our function will be

=BASE(C4,10,6)

And then we drag the Fill Handle to fill the rest of the cells.

BASE Function in Excel

Note: BASE() Function is only available in Office 365.


Method 4: Using CONCATENATE(), REPT() and LEN() Function

REPT() Function repeats a character a specified number of times.

Syntax for REPT() Function is

=REPT(Character,Times_to_repeat)

If you want to know more about REPT() Function, visit this link.

And the LEN() function returns the length of any text.

Syntax for LEN() Function

=LEN(text)

So the formula

=REPT(0,6-LEN(C4))

Will return three zeros. And we can again CONCATENATE it with C4 to get 000111.

So the total formula will be

=CONCATENATE(REPT(0,6-LEN(C4)),C4)

REPT and LEN Function in Excel


4. Use VBA Code to Add Leading Zeros in Excel

You can also use this VBA Code to add leading zeros in Excel.

  • Click Alt + F11 on your keyboard. It will open the VBA (Visual Basics for Application) window in your Excel file.
  • In the VBA toolbar, click on Insert. Then click on Module.

Using VBA Code

And you will get a window like this.

VBA Window in Excel

  • Write the following code there.

Code

Function AddLeadingZeroes(ref As Range, Length As Integer)
    Dim i As Integer
    Dim Result As String
    Dim StrLen As Integer
    StrLen = Len(ref)
    For i = 1 To Length
        If i <= StrLen Then
            Result = Result & Mid(ref, i, 1)
        Else
            Result = "0" & Result
        End If
    Next i
    AddLeadingZeroes = Result
End Function
  • It creates a function called AddLeadingZeros
  • The Function takes two arguments, a range of cells named Range, and an integer named Length.
  • It returns an output with a specified number of leading zeros to the numbers (total length of the numbers will be Length) given as input.

This site has helped us understand and develop this procedure.

Read More: How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)


Conclusion

These are the ways you can use to insert a specified number of leading zeros before numbers in Excel. Do you know any other method? Let us know in the comment section.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo