If you are working with **Microsoft Excel**, you might find that leading zeros before numbers are removed automatically. Excel’s default options remove the leading zeros from numbers. In this article, I am going to show you how to **add leading zeros** to make 10 digits in excel.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 10 Suitable Ways to Add Leading Zeros to Make 10 Digits in Excel

In the following, I have described 10 simple and suitable ways to add leading zeros to make 10 digits in excel.

Suppose, we have a dataset of some **Employee Name **and their **Contact Number**. Now, I will add leading zeros before the numbers to make 10 digits.

### 1. Use Format Cells to Add Leading Zeros in Excel to Make 10 Digits

However, If you are looking for a simple way to add leading zeros and make 10 digits in excel, then you are at the right place. In this method, I will show you how I made 10 digits using the format cells feature of excel.

**Steps:**

- Firstly, select the contact numbers placed in
**cells**(**C5:C11**). - Later, press
**Ctrl+1**to open the “**Format****Cells**” window.

- Secondly, in the format cells window hit the “
**Custom**” button and put “**0000000000**” in the type section. - Thereafter, hit
**OK**to continue.

- As a result, we have our 10-digit output adding leading zeros before the numbers.

### 2. Apply Text Format to Insert Leading Zeros to Make 10 Digits

Although, you can also change the **cell format to text format** and manually **put zeros before the numbers** to reach to your destination.

**Steps:**

- First, choose a list of numbers from the table. Here I have chosen
**cells**(**C5:C11**). - At the same time change the format to the “
**Text**” format from the home ribbon.

- Afterward, manually put
**zeros**before the numbers. - Don’t worry. The leading zeros won’t go away as we converted those selected cells to “
**Text**” format.

- As soon as you fill those cells an “
**Error**” sign will appear in a corner. - But you can remove them by clicking the “
**Error**” icon and pressing “**Ignore****Error**”.

- Here, we have successfully got 10-digit numbers in all cells by adding leading zeros.

**Read More:** **How to Add Leading Zeros in Excel Text Format (10 Ways)**

### 3. Perform TEXT Function to Add Leading Zeros to Construct 10 Digits

In spite of using the text format feature, you can apply the **TEXT function** in excel to add leading zeros to make 10 digits.

**Steps:**

- Select a
**cell**to write the formula. Here I have selected**cell**(**E5**). - Apply the formula-

`=TEXT(C5,"0000000000")`

Where,

- The
**TEXT function**converts the number into text format within a string.

- Hereafter, press the
**Enter** - Next, drag the “
**fill****handle**” to fill all the cells.

- In conclusion, you will get your desired output in a new column with 10 digits adding zeros before the numbers.

### 4. Add Apostrophe Sign Before Numbers to Add Leading Zeros in Excel

Specifically, you can add an apostrophe sign (**‘**) before the numbers to **keep leading zeros** in excel. Follow the steps below-

**Steps:**

- First of all, select a
**cell**(**C5**) and add an apostrophe sign**(‘)**before the number adding zeros.

- Meanwhile, you will see the output with zeros in front of the cell.

- Hence, do this process for all the cells in the table.
- Even though the leading zeros will be added to the table but you will find an “
**Error**” sign with all the numbers. - For this reason, choose all the cells with
**error**.

- Therefore, click the “
**Error**” icon, and from the drop-down list press “**Ignore****Error**”.

- Eventually, we reached our destination adding leading zeros to construct numbers to 10 digits.

### 5. Utilize RIGHT Function to Cast Leading Zeros to Make 10 Digits

Differing from these manual methods use the **RIGHT function** to cast leading zeros to construct 10 digits.

**Steps:**

- Here select a
**cell**(**E5**) to apply the formula. - Now, put the formula down-

`=RIGHT("0000000000"&C5,10)`

- Afterward, hit the
**Enter**button to continue. - Hence, pull the “
**fill handle**” down.

- Finally, you will get the precious result by adding leading zeros to make 10 digits numbers in excel.

### 6. Add Leading Zeros to Make 10 Digits with Excel BASE Function

You can also utilize the **BASE function** equally to add leading zeros before all the numeric values in the cell.

**Steps:**

- Yet we will select a
**cell**(**E5**) to write the formula. - Apply the formula-

`=BASE(C5,10,10)`

Where,

- The
**BASE function**returns a numeric value into text format.

- In the same fashion, click
**Enter**to complete the formula and get the output for the applied formula. - Alongside, drag the “
**fill handle**” down to fill.

- Specifically, in the final output column, we will get the finished product.

### 7. Use Power Query’s PadText Function to Include Leading Zeros

**Power Query** is a tool to analyze and organize data which is mainly used for data analysis. With this feature of **Excel**, you can import data from various sources and shape and convert it according to your choice. In this method, I am explaining how to add leading zeros in excel to make 10 digits using the power query’s **PadText function**.

Suppose, you have a list of numbers saved on your PC. Now, we will import the data in excel using the “**Power** **Query**” tool and then apply the** PadText function** to construct to 10 digits.

**Steps:**

- In the first step, open your workbook and go to
**Data > Get Data > From File > From Text/CSV**.

- Eventually, a new window will pop up named “
**Import****Data**”. - Once clicking the file click “
**Import**”.

- Consequently, the data will be imported to your excel worksheet.
- Then click “
**Transform****data**”.

- Later the “
**Power Query Editor**” will open. - At first press the “
**Custom****Column**” option from the “**Add****Column**”.

- Therefore, a new window will pop up named “
**Custom Column**”. - From the new window, name the column name of your choice and apply the following formula-

`=Text.PadStart([Column1],10,"0")`

- Press
**OK**to continue.

- On the contrary, our contact number list is ready with leading zeros.
- Now to get them in our excel worksheet click the “
**File**” option.

- Below choose “
**Close & Load**” to get the final output.

- Thus our final result is ready with 10 digits adding zeros in front of numbers in a new worksheet.

### 8. Combine REPT and LEN Functions to Join Leading Zeros in Excel

In **Microsoft Excel** using functions, you can complete any task you want. With the combination of the **REPT** and **LEN **functions, you can attach leading zeros just before numeric values and make 10 digits in excel.

**Steps:**

- For applying the formula select a
**cell**(**E5**). - Write the following formula down-

`=REPT(0,10-LEN(C5))&C5`

Where,

- The
**REPT function**repeat characters a defined number of times. - The
**LEN function**demonstrates the length of a text string as the number of characters.

- Hence, click
**Enter**. - Afterward, pull the “
**fill****handle**” down to fill the column.

- Finally, using functions we got our 10-digit number by adding zeros before the numbers.

### 9. Excel VBA to Adjoin Leading Zeros

Fortunately, you can try the **VBA code** from the following to adjoin leading zeros before the numbers.

**Steps:**

- Presently, choose
**cells**(**C5:C11**) and press**Alt+F11**to open the “**Microsoft Visual Basic for Applications**” window.

- Therefore, in the new window open a “
**Module**” from the “**Insert**” option.

- Put the following code and press “
**Run**” to apply the code to those chosen**cells**–

```
Sub AddLeadingZeroes()
Range("C5", Range("C5").End(xlDown)).Select
Selection.NumberFormat = "0000000000"
End Sub
```

- Thus the cells will be added zeros before the numbers making it 10 digits.

### 10. Apply DAX Formula to Attach Leading Zeros

If you want you can apply the **DAX formula** to attach leading zeros before numbers in excel. In this method, I have shared steps to add zeros before numbers to make 10 digits in excel.

**Steps:**

- Here select the whale dataset and click “
**Pivot****Table**” from the “**Insert**” option.

- Inside the worksheet choose a cell where you want to create the pivot table choosing “
**Existing****Worksheet**”. - Now, press
**OK**to continue.

- As soon as clicking
**OK**a right pane will appear named “**PivotTable Fields**”. - Hence, place the cursor over the “
**Range**” menu and right-click the mouse button to get options. - Hereafter, press “
**Add Measure**”.

- Then
**name**the list according to your choice and place the formula in the “**Formula**” section-

`=CONCATENATEX(Range,FORMAT([Contact Number],"0000000000"),",")`

- Accordingly, hit the
**OK**button to continue.

- In conclusion, you will get the desired result in the selected cell.

**Read More:** **How to Add Leading Zeros in Excel by CONCATENATE Operation**

## Things to Remember

- Suppose you have numbers in your workbook with the same amount of numeric values in every cell. In that case, you can add a fixed number of leading zeros before the numbers using
**the CONCATENATE function**. To learn more, follow**this article**.

## Conclusion

In this article, I have tried to cover all the effective methods to add leading zeros to make 10 digits in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the **ExcelDemy** team, are always responsive to your queries. Stay tuned and keep learning.