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.

**Table of Contents**hide

**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)**