MS Excel makes our life super easy with its functioning formulas and features. You can quickly compose your specific data sheet with the help of this fantastic app.
Suppose you are processing some data into an Excel sheet, but the format of those data is not the same. Then to make those values more eye comforting, you can easily add leading zeros before those values.
You can quickly add some leading zeros into your Excel spreadsheet by converting the values into TEXT format. Also, you can use the TEXT, CONCATENATE, RIGHT or BASE formulas to complete this action.
I will show you the working formulas and VBA codes in this article, so don’t skip any parts.
Let’s jump in!
How to Add Leading Zeros in Excel
If your Excel workbook contains different numbers with different lengths, then you can add zeros in front of them to make those numbers eye-cache.
You can use several methods to complete this operation, but the most simple and functioning ways are stated in this article, so read along.
You may also like to read how to freeze a row on Excel easily.
Here are the steps to add leading zeros to the Microsoft Excel sheet:
1. Format the Cell Values into Text
When you enter a value in an Excel cell and place zeros in front of those values, the zeros don’t appear. So you can convert those values into text, and then the leading zero will arise on your Excel sheet.
Here are the steps to format the cell values into text in Excel:
- Select the specific cells from MS Excel.
- Right-click and choose the Format Cells option.
- Choose the Text option from the Number section.
- Hit the OK button.
- Place your desired leading zeros in those formatted cells.
- Click on the Exclamatory( ! ) sign.
- Select the Ignore Error option.
You will immediately notice that, unlike previous times, your added leading zero does not go away when you click on the other cell.
Use Apostrophe
You can also recreate the above process using an Apostrophe before your number. Let’s find out how you can use an Apostrophe to add some leading zeros to an Excel sheet.
Here are the ways to use an apostrophe to add zeros in front of Excel:
- Click on an Excel cell.
- Type an Apostrophe( ‘ ), then write your number.
- Select the Exclamatory( ! ) sign.
- Click on the Ignore Error option to remove the error sign.
With the help of this Apostrophe method, you can easily add your required leading zeros to your worksheet.
2. Format Cell as Custom and Include Zero in the Type Box
The process of adding some leading zeros into an Excel sheet is straightforward. You can use the Format Cells Custom feature to complete this action.
Also, check out our separate post on how to randomize a list in Excel
Let’s find out how you can easily add zeros in front of an Excel workbook.
Here are the steps to format cells as custom and add zero in the type box:
- Highlight the entire specific cells.
- Right-click and choose the Format cells option.
- Select the Custom option from the Category box.
- Click on the 0 option below the General option.
- Write the desired number of zeros in the Type box.
- Look at the Sample, and when it’s appropriate, hit the OK button.
You will see that desired zeros are added in front of your Excel data.
3. Use the Text Formula 
The text formula can help you add leading zeros to your Excel data sheet. Let’s sort out how you can use the Text formula to add zeros in front of your desired Excel number.
Here are the steps to use the Text formula to add leading zeros in Microsoft Excel:
- Click on the Next cell beside the desired cell from the Excel workbook.
- Type =TEXT(Cell Value,”The number of zero you want”) ; Example: =TEXT(E2,”0000″).
Select the new formatted cell and drag its Fill Handle to your desired location. 
Immediately you will find that all the previous cells are now in leading zero formats.
4. Apply the REPT & LEN Function 
With the REPT and LEN formulas, you can add your desired zero in front of your Excel data. But before jumping into the procedure, let’s understand how this formula works.
Formula: =REPT(0,5-LEN(E2))&E2
Here you can see that,
0 = The number you want to add in front of your data.
5-LEN(E2) = A total of 5 digits will appear, and in my case, in the E5 cell, there is 1 digit, so the other 4 digits become zero.
Here are the steps to apply the REPT & LEN formula:
- Select the adjacent cell beside the specific cell.
- Type =REPT(0,5-LEN(E2))&E2, I use the E2 cell, and you must use your particular cell.
- Press the Enter key.
Drag the Fill Handle of the formula cell to your specific row. 
From the pictures, you can see that leading zeros are added perfectly. You can change the digit limit if you want to add more zeros. Such as =REPT(0,9-LEN(E2))&E2; See the picture for that equation, and there are 9 numbers, including zero.
5. Use CONCATENATE Formula 
The Concatenate formula can make your task easy because with this formula, you can add leading zeros into your Excel sheet. For your better understanding, I have stated the formula and its function.
Formula: =CONCATENATE(“0000”,E2)
“0000” = The number of zeros you want to add.
E2 = The cell number that you want to follow.
Here are the steps to use the CONCATENATE formula to add leading zeros in MS Excel:
- Click on the cell beside the specified cell.
- Write =CONCATENATE(“0000”,E2), I want to display 4 zeros, and my specified cell number is E2.
![conca-formula-excel]](https://10pcg.com/wp-content/uploads/conca-formula-excel.jpg)
- Hit the Enter button.
- Select the Fill Handle of that cell and drag it according to your need.
From my picture, you can see that from cells F11 to F14, there are 6 digits, including zero. So to make them equal, I format the formula =CONCATENATE(“000”,E2), which means 1 zero less.
You may also like to read how to freeze a row on Excel easily.
6. Use Base & Right Function
With the help of the Base and Right formula, you can add zeros in front of your Excel workbook. Let’s sort out the procedure and how to use them.
Base formula: =BASE(E2,10,5)
E2 = Cell number.
10 = The base at which you want to format your numbers.
5 = The total number, including the zero you want to appear on your Excel sheet.
Right formula: =RIGHT(“0000″&E2,4)
“0000”&E2 = The zero format you want and a specific cell.
4 = Total number including zero you want to add.
Here are the steps to use the Right & Base formula:
Base Formula
- Click on an adjacent cell close to the primary cell.
- Type =BASE(E2,10,5); you must type your desired cell number and digit into the formula.
- Press the Enter key.
- Drag that cell’s Fill Handle according to your requirement.
Right Formula 
- Select a cell close to the specific cell.
- Type =RIGHT(“0000″&E2,4); you must insert your desired value into this formula.
- Hit the Enter button.
- Click on the Fill Handle of that cell and move it to your appropriate position.
See how easy the formula is? And now is the time for you to try them.
7. Apply the VBA Code
You can build a custom function with the help of the VBA module. This custom function can add leading zeros to your Excel spreadsheet.
Here are the steps to apply the VBA code to add leading zeros in Microsoft Excel:
- Press the ALT+F11 keys to open the VBA window.
- Click on the Insert section and Select the Module option.
- Copy and paste the stated code into the Module window:
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
- Exit the VBA window.
- Click next to the primary cell.
- Type =AddLeadingZeroes(Original Cell,5) here, 5 is the total number, including the zeros. You can modify this formula according to your need.
- Drag that cell handle and place it in your required area.
Though this method can become tricky for some users, I recommend the other formulas I have shown in this content.
Some more guides on how to post bank statement in Excel to quickbooks?
How to Remove Leading Zeros from Excel Using the VALUE Formula
You can follow several methods to remove the leading zeros from the Excel worksheet. But inserting the Value formula is the simplest and quickest among them.
Let’s head out and see how you can finish this operation.
Here are the steps to remove leading zeros from an Excel file:
- Select the next cell beside the original cell.
- Type =VALUE(Original cell number) such as =VALUE(C2).
- Hit the Enter key.
- Drag that cell’s Handle according to your need.
Also, you can Highlight those specific cells and right-click then select the Format Cells option. After that, choose the Number option to remove the leading zero format in MS Excel.
Immediately you will notice that leading zero formats are gone from your Excel sheet.
Follow our guide on how to shade every other row in Excel.
FAQs
What is the CONCATENATE formula to add leading zeros in MS Excel?
The CONCATENATE formula to add zeros in front of numbers in Excel is =CONCATENATE(“0000”,E2). Change the zero format and cell number according to your need.
How do I write the Text formula to add zeros in front of numbers in Excel?
The Text formula for adding zeros into Excel is =TEXT(Cell Value,”The number of zero you want”) such as =TEXT(E2,”0000″).
How do I add leading zeros in a Microsoft Excel sheet?
You can use the Base formula to add zeros in MS Excel, and the formula is =BASE(E2,10,5). You must alter the input according to your specific need.
Bottom Line
Sometimes while using the Excel app, we want to make our worksheet easy to read or understand.
In those cases, adding some leading zeros into our Excel sheet can do the trick. From this article, you already know how easily you can complete this operation using some fantastic Excel formulas. Also, I displayed some options like Format Text which will offer you the same results.
If you have any questions regarding this article, let me know in the comment.
