While working on an Excel worksheet, you may see that Excel doesn’t allow 0 before any numbers. So, you can’t make a column with the same number of digits. If you just put a 0 before other digits and press Enter, Excel will not show the 0 but the other digits only. But there are some easy and tricky methods to put 0 in Excel in front of numbers. In this article, you will learn 5 of them.

## How to Put 0 in Excel in Front of Numbers: 5 Effective Methods

Suppose you have some data of numbers which are of different digits. But, you want to make the numbers of the same digits to make the worksheet look good. For this, you want to add several 0 in front of the numbers. Here, I am showing you 5 methods to put 0 in front of numbers in Excel.

### 1. Convert Numbers to Text Format to Add Leading 0 in Excel

If you convert numbers format to text, then you can type as many leading zeroes as you want. Excel will not vanish them. Follow the steps.

**📌**** STEPS:**

- Simply, you can use an
**Apostrophe(‘)**before starting a number to add 0 at the front. For this, just start the number with an Apostrophe. But, the Apostrophe will not show in the cell also there will be an error warning.

Similarly, put an Apostrophe in all cells and add the necessary zero as you want.

**💬**** NOTES: **

- Adding an Apostrophe at the front will convert the number to
**Text**format. So, you can’t use them for any calculation, - There, in the top left corner of the cells, you see a green tiny arrow symbol. After clicking on the arrow, a message will come saying “
**Number stored as text**”. To continue as it is, click on the option “**Ignore Error”** - Using this method will not automate the other cells. You have to add Apostrophe and 0 in all cells manually.

You also can do it directly. Follow the steps below.

- Firstly, select the cells of the column.
- Then, go to the
**Number**tab on the top ribbon. And open the drop-down menu. - From the format options, select the
**Text**option.

- As an outcome, the cells will become in
**Text**format. - Then, you can add necessary zeros in front of numbers.

**💬**** NOTE:**

- By this method, you convert the numbers to
**Text**format. So, you can’t use them for any calculation.

**Read More:** How to Add Leading Zeros in Excel Text Format

### 2. Use Custom Formatting

Alternatively, you can use the **Custom Format **option to make the format of the cell as you want. To do this, follow the steps below.

**📌**** STEPS:**

- First, select the cells containing the numbers.
- Then, go to the top ribbon and in the
**Number**Tab, you find an arrow in the bottom right corner of the tab. Then press on the arrow. See screenshot.

- As a response, there will be a window named ‘
**Format Cells’.** - Then, stay in the
**Number**tab and go to the**Custom**option. - And, in the
**Type**box, write ‘**0000′**(only zeros, not the quotes) to make a 4-digit number. Excel will add the necessary 0 in front to make the numbers of 4 digits. - Finally, press the
**OK**button.

- As a result, you will see the
**Output**column numbers have become 4 digits and there are necessary 0’s in front.

**💬**** NOTES: **

- By this method, the numbers will remain in
**Number**format. So, you can do any calculations with them. - If you do copy and paste them as values, it will lose the 0’s in front.
- In the formula bar, you will see the original value without any 0’s in front.

**Read More:** How to Add Leading Zeros to Make 10 Digits in Excel

### 3. Utilize the Ampersand (&) Symbol

In Excel, you can use the **Ampersand **symbol to add to values of two cells but in **Text **format. So, you can use this to add extra 0’s in front of the numbers. To do this, follow the steps below:

**📌**** STEPS:**

- First, write this in cell
**C5:**

`="00"&B5`

- So, it will add two zeros in front of the number of cell
**B5**.

- Now, copy the formula and paste it into other cells to do the same thing. Also, you can use the
**Fill handle**button**.**

**💬**** NOTES:**

- By this method, you convert the numbers to
**Text**format. So, you can’t use them for any calculation. - And, it will add 2 zeros to all cells, so it will not make numbers of the same digits.

### 4. Utilize Excel Functions to Get 0 in Front of Numbers

In Excel, there is an enormous number of functions that have made our work easier. So, we can use some function to add 0 in front of numbers in Excel.

#### 4.1 Using REPT Function

The purpose of the **REPT **function is to repeat a text or character a specified number of times.

**Syntax: **

**=REPT (text, number_times)**

**Text**: The text or character that will be repeated

**Number_times:** It is the number of times to repeat the text.

**📌**** STEPS:**

- First, input this formula in cell
**C5**.

`=REPT(0,2)&B5`

- Then, copy and paste the formula into the other cells.
- As a result, two zeros will be added in front of all the numbers.
- So, It will not make numbers of the same digits.

To make numbers of the same digits, you will have to use the **IF**** and LEN** functions together with the **REPT **function. Follow the steps below:

- At first, write this formula into cell
**C5**.

`=IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5)`

**🔎 Formula Explanation:**

**LEN(B5):**It calculates the number of characters in cell**B5****LEN(B5)<6**: It is the condition that if the number of characters in cell**B5**is less than 6**6-LEN(B5):**It will give the value that is needed to make the number of 6 digits.**REPT(0,6-LEN(B5))&B5:**It will add the required zeros in front with cell**B5****=IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5):**If the**B5**cell value is less than 6 digits then it will add the required digits to make 6 in front of the number else it will give the value of cell**B5**.

- Finally, copy and paste the formula to other cells using the
**Fill Handle**icon or shortcuts.

**💬**** NOTES:**

- Using only the
**REPT**function will only add digits in front of the number but it will make all the numbers of a required digit. - However, using the
**IF**function with the**REPT**function will allow you to make the cells of the same number of digits. - As the cells remain in
**Number**format, you can use them for any calculations.

#### 4.2 Using TEXT Function

Also, you can use the **TEXT **function to convert the number to **T****ext** format and of specified digits.

**Syntax**

**=Text(Value, format_text)**

**Value**: It is the number that you will convert to text

**Format_text:** It is the format that you want to use.

**📌**** STEPS:**

- For this, write this formula into cell
**C5**

`=TEXT(B5,"0000")`

- So, it will convert the value of cell
**B5**to**T****ext**format and will add the necessary 0’s to make it 4 digits.

- Then, copy and paste the formula to other cells using the
**Fill Handle**icon or shortcuts**Ctrl + C**and**Ctrl + V.**

#### 4.3 Using CONCAT (or CONCATENATE) Function

You can use the **CONCAT **or **CONCATENATE **function to merge or add the characters of two cells. So, you can use this function to add 0’s in front of the cells.

**📌**** STEPS: **

- For this, write the following formula in the cell
**C5**

`=CONCAT("00",B5)`

- Then, use the
**F****ill Handle**icon to copy and paste the formula to the other cells.

**💬**** NOTE:**

Using the **CONCAT **function, it will just add two zeros to the cells so it will not make the cells of the same digit.

#### 4.4 Using RIGHT Function

Generally, we use the **RIGHT **function to take certain digits of a cell from the right. So, to add 0 in front of numbers to make them of any specified digits, you can use the Right formula.

**Syntax**

**=RIGHT (text, [num_chars])**

**Text:** From which you will extract characters from the right.

**Num_chars:** It is the number of characters that will be extracted from the right

Follow the steps below:

**📌**** STEPS:**

- Firstly, write the following formula in cell
**C5**.

`=RIGHT("00"&B5,4)`

- Then, use the
**Fill Handle**icon to copy and paste the formula to the other cells.

#### 4.5 Using BASE Function

We use the **BASE **function to convert numbers to another base. To use this function follow these steps.

**Syntax:**

**=BASE (number, radix, [min_length])**

**Number**: It is the number that will be converted. It must be an **integer** value and **>= 0**.

**Radix**: It is the base radix to which the number will convert. it must be **>=2** or **<=36**.

**Min_length**: It is the minimum length of the string

**📌**** STEPS:**

- Firstly, write the following formula in cell
**C5**.

`=BASE(B5,10,4)`

- Then, use the
**Fill Handle**icon to copy and paste the formula to the other cells.

**Read More: **How to Add Leading Zeros in Excel

### 5. Use the Power Query Tool

We use the **Power Query** to transform data in Excel. So, it is possible to add 0 in front of numbers. To do this, follow the steps below.

**📌**** STEPS:**

- First, go to the
**Data**tab >**Get Data > Launch Power Query Editor.**

- Then, there will open a window named “Power Query Editor”.
- In the window, go to the
**Home**tab, press on the**New Source**drop-down menu, and select**Excel Workbook.** - Then, select a workbook and extract data from it.

- Alternatively, select the option
**Enter Data**and input data manually.

- Here, make a column by pasting data from the workbook or inputting data manually. Then press
**OK.**

- After that, go to the
**Add Column**tab and press on the**Custom Column**option.

- Then, there will open a window named
**“Custom Column”.** - After that, give a suitable name for the column.
- Then, paste the formula given below:

`=Text.PadStart([Number],4,"0")`

- Then, press
**OK.**

- Doing that, you will see a column named “Output” and the cells are of 4 digits with necessary zeros in front.
- Finally, press on
**Close & Load.**

- Then, there will open a new sheet in the workbook with the data from the
**Power Query.**

## Things to Remember

- Using an
**Apostrophe**at the front will convert the number to**Text**format and you have to 0 in front manually. **Custom Formatting**is the most useful option to add zero and make numbers of the same digits.- Formatting numbers to
**Text**will allow you to add 0 in front but it will hamper doing calculations. **REPT**function will add the specified amount of zeros no matter the number of digits. So, to make the number of the same digits, you have to use the**REPT and IF**functions together.**TEXT**function does the work of**Custom Formatting.****CONCAT**function will also add the given amount of zeros no matter what the number of digits. So it will make the numbers of the same digit.**RIGHT & BASE**function will be useful to make the numbers of the same digit.- And finally,
**Power Query**will help to extract data and format them from external sources.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

In this article, I have tried to show you how to add 0 in front of numbers in Excel. Here, I have used 5 methods and some formulas to add 0 in front of numbers in Excel. I believe you have learned the methods from the article and will practice these yourself. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel**