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.
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.
- Click on the drop-down menu. You will get these options. Scroll down and click on the last option, Text.
- 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.
- You see a small Error Box on the left of the first cell. Click on it. From the options available, click on Ignore Error.
- 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.
- You will get a dialogue box called Format Cells. In the Category option, select Custom. And in the Type option, write “000000”
- Then click OK. You will get leading zeros inserted before the selected the contents of the selected cells, like this.
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.
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.
3. Use Excel’s Functions to Add Leading Zeros in Excel
Method 1: Using TEXT() Function
The syntax of TEXT() function is
- 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
Then we 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
- 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
Now we drag the Fill Handle to fill the rest of the cells.
We can also do the same operation by using Ampersand Symbol (&). Ampersand Symbol (&) does the same function as CONCATENATE().
The formula then will be:
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
- 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:
Or you can also use:
Method 3: Using Base() Function
The syntax for BASE() function is
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
And then we drag the Fill Handle to fill the rest of the cells.
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
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
So the formula
Will return three zeros. And we can again CONCATENATE it with C4 to get 000111.
So the total formula will be
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.
And you will get a window like this.
- Write the following code there.
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.
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.
- How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- Excel Round to Nearest 100 (6 Quickest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- How to Round Off Numbers in Excel (4 Easy Ways)
- How to Round to Nearest Multiple of 5 in Excel (3 Easy Ways)
- Excel Number Stored As Text [4 Smart Ways]
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- How to Round Numbers in Excel Without Formula (3 smart ways)
- How to Apply Accounting Number Format in Excel! (Easy way)