Sabrina Ayon
In Microsoft Excel, leading zeros can be extremely useful in a variety of applications. A leading zero is any “0” digit in a number string that comes before the first nonzero digit. Sometimes we may have come across a circumstance when we needed to add a leading zero in Excel. In this article, we will demonstrate how to add leading zeros in Excel text format.
Download Practice Workbook
You can download the workbook and practice with them.
10 Easy Ways to Add Leading Zeros in Excel Text Format
While working in excel, we may need to keep records of zip codes, phone numbers, banking information, and security numbers. But if we type “0011 20 010”, excel automatically removes the leading zeros. There are so many ways to add leading zeros in excel.
For adding zeros in front of any values we are going to use the following dataset which contains some values in column B and we want to put some zeros in front of those values in column C. Let’s go through the ways to add leading zeros in excel.
1. Insert Apostrophe (‘) to Add Leading Zeros in Excel Text Format
We can make Excel enter a number as text by inserting a leading apostrophe. This is the quickest and easiest way to put zeros in front of a value while entering the data. Let’s have a look at the steps to do this in multiple cells.
STEPS:
- Firstly, type the special character, apostrophe “ ‘ “ before any value. So, we type ‘000011 in cell C5.
- This instructs Excel that the data is intended to be text rather than a number.
- Secondly, press Enter.
- The leading zeros are still visible in your data. But showing an error with a green triangle.
- Now, click on that triangle, and from there select Ignore Error.
- The error is now removed by doing this.
- You can put all the values by using the apostrophe before entering the data by just following the above steps.
Read More: [Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)
2. Add Leading Zeros in Text Format by Using Format Cells
With Excel Format Cells we can customize our data. To add leading zeros in excel text format we can use the format cells feature. Let’s have a look at the procedure down to add zeros on lead.
STEPS:
- In the first place, select the cells where you want to add leading zeros. So, we select cell range C5:C10.
- In the second place, right-click on your mouse and select Format Cells. This will open the Format Cells dialog.
- Another way to open the Format Cells dialog box is just to press Ctrl + 1.
- After that, go to Numbers and from the Category section, select Custom.
- Then, type as many zeros as your preference in the type box under Type. so, we type “00000”.
- Next, click on the OK button.
- And, that’s it. By following the steps, you will be able to add leading zeros to your data.
Read More: Excel VBA: Format Cell as Text (3 Methods)
3. Number Command to Include Leading Zeros in Excel Text Format
We can change the format of the cells by using the number command. If the cells are in text format only then we are able to enter the values with leading zeros. Now, let’s follow the steps down to change the format of those cells to add zeros on the leading of the values.
STEPS:
- First, choose the range of cells. So, we choose range C5:C10.
- Second, go to the Home tab from the ribbon.
- Further, from the drop-down menu under the Number command, select Text.
- Now, put the values with leading zeros in those range of cells.
- You may face an error, likewise the previous method in section 1. Click on that error, and select Ignore Error.
- And, finally, there you go! Now, we are able to see the values with leading zeros.
Read More: How to Add Text after Number with Custom Format in Excel (4 Ways)
4. Apply TEXT Function to Add Leading Zeros in Excel
In Excel, the TEXT function is used to transform integers into text. It essentially converts a numeric number to a text string. We can use the TEXT function to add leading zeros in excel by following the simple steps down.
STEPS:
- In the beginning, select the cell where you want to put the zeros in the font of the values. So, we select cell C5.
- Then, write down the formula there.
=TEXT(B5,"00000#")- After that, press Enter. And, you will be able to see the formula in the formula bar.
- Now, drag the Fill Handle down to copy the formula over the range.
- And finally, you can see that the formula adds zeros before the values.
Read More: Excel Convert Number to Text with Leading Zeros: 10 Effective Ways
5. Add Leading Zeros with Excel RIGHT Function
Excel RIGHT function returns the provided set of characters from a text string’s end. This function can be used to add zeros before any values. Now, let’s go through the steps to add leading zeros using the excel RIGHT function.
STEPS:
- By the same token as earlier methods, choose cell C5.
- Further, type the formula in that cell.
=RIGHT("000000"&B5, 6)- After that, press the Enter key from your keyboard.
- Further, drag the Fill Handle down to duplicate the formula.
- In the end, you will get the zeros before the values.
Read More: How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)
Similar Readings
- How to Change Font Size of the Whole Sheet with Excel VBA
- Excel VBA Codes to Bold Text in a String (5 Examples)
- How to Convert Text to Time Format with AM/PM in Excel (3 Methods)
- [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
- How to Write 001 in Excel (11 Effective Methods)
6. Combine REPT and LEN Functions to Add Leading Zeros in Text Format
The REPT function can be used to fill a cell with numerous incidents of a text string. And, the LEN function calculates how many characters are in a supplied text string. We can add leading zeros by combining those two functions. Let’s go along with the procedure down to add leading zeros.
STEPS:
- Firstly, as similar to the methods before, select cell C5.
- Secondly, enter the formula into that cell.
=REPT(0,5-LEN(B5))&B5- After that, hit the Enter key. In the formula bar, the formula will appear.
🔎 How Does the Formula Work?
⏩ REPT(0,5-LEN(B5)): This repeats characters a given number of times.
Output → 000
⏩ REPT(0,5-LEN(B5))&B5: This returns the value with leading zeros.
Output → 00011
- Now, drag the Fill Handle down to copy the formula.
- And, that’s it. The final result is in your resulting range of cells.
Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)
7. CONCATENATE Function to Insert Leading Zeros
The CONCATENATE function is a text function that can be used to combine two or even more text data into a single string. To add leading zeros we can use the Excel CONCATENATE function. To use the function, let’s follow some simple steps below.
STEPS:
- Correspondingly as previous methods, select cell C5.
- Then, enter the formula.
=CONCATENATE(0,B5)- Now, press the Enter key. And the formula will appear in the formula bar.
- To copy the formula over the range, drag the Fill Handle down.
- And, there you go! Zeros were added before in the values.
Read More: How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)
8. Insert BASE Function to Add Leading Zeros in Excel
The BASE function in Excel translates a number to its text representation in a specific base. With the BASE function, we can add leading zeros. Let’s demonstrate the procedure for that.
STEPS:
- First, choose cell C5.
- Second, write the formula.
=BASE(B5,10,6)- Now, press Enter.
- After that, drag the Fill Handle to repeat the formula over the range.
- Finally, by using this formula we can now see our desired result in column C.
9. VBA to Add Leading Zeros in Excel Text Format
Excel VBA always completes the assignment in the same manner as excel functions or manual tricks. We can use Excel VBA to add leading zeros. Let’s see the steps to do this.
STEPS:
- First, go to the Developer tab on the ribbon.
- Second, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is, simply right-click on the sheet and select View Code.
- This will open the Visual Basic Editor where you can write the code.
- Next, write down the VBA Code below.
VBA Code:
Sub ADD_Leading_Zeros(r As Range, zeros As Integer)
Dim str As String
Dim i As Integer
For i = 1 To zeros
str = str & "0"
Next
r.NumberFormat = "@"
r = Evaluate("index(text(" & r.Address & ", """ & str & """),)")
End Sub
Sub Result()
With ActiveSheet
ADD_Leading_Zeros .Range("C5:C10"), 5
End With
End Sub- Now, run the code by pressing the F5 key or clicking the Run Sub button.
- You may see an error, which indicates that the numbers are formatted as string or text.
- Now, click on Ignore Error from the error drop-down option.
- And, finally, you can see using the VBA code we can add leading zeros in excel.
Read More: How to Format Text in Excel Cell (10 Approaches)
10. Using Power Query to Add Leading Zeros
Power Query is an Excel feature that is ideal for users who require data preparation but do not require a full array of analytic capabilities. The Power Query Editor is the main data preparation interface, where you may connect to a variety of data sources and preview data while applying hundreds of different data transformations. We can add leading zeros with the help of the power query. For this, we need to follow the steps down.
STEPS:
- Firstly, select the range of the data cell. So, we select range B4:B9.
- Secondly, go to the Data tab from the ribbon.
- Thirdly, select From Table/ Range under Get & Transform Data.
- This will open the Create Table dialog box.
- Next, click on the OK button.
- This will create a table. We name the table Actual Value.
- Now, again to open the Power Query Editor, from the ribbon, select the Data tab. Then, under Get & Transform Data, pick From Table/ Range.
- After that, you can see that this will open the Power Query Editor.
- Further, go to Add Column on the power quiet editor.
- Furthermore, select Custom Column.
- This will appear in the Custom Column window.
- Name the new column Value with leading Zeros. And, write the formula.
=Text.PadStart([Actual Value],6,"0")- Then, click OK.
- But you can see there is an error, it is because the values are in number format and we have to convert the value to text format.
- To do that, go to Source in the Query Settings.
- Then, from the ABC123 drop-down menu, select Text.
- This will open a window named Insert Step. Click Insert.
- And finally, if you go to Added Custom on the Query Settings. You can see that the zeros are now added on the lead of those values.
- In the end, a power query sheet will be added to your spreadsheet with those values. And, the sheet name is Table1.
Conclusion
The above methods will assist you to add leading zeros text format in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
Related Articles
- Format Cell and Center Text with Excel VBA (5 Ways)
- How to Change Lowercase to Uppercase in Excel Without Formula
- Change Case in Excel without a Formula (5 Ways)
- How to Capitalize the First Letter in Excel (3 Methods)
- Keep Leading Zeros in Excel CSV (4 Easy Ways)
- How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)
