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 show an error warning.

Similarly, put **Apostrophe** in all cells and add 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, 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 T
**ext**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 open 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:**

- At 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. - But, 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, so 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
**fill 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 Keep 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:**

- At 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 and 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 in 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**function 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

**How to Keep Leading Zeros in Excel CSV****How to Remove Leading Zeros in Excel****How to Write 001 in Excel****How to Add Trailing Zeros in Excel****How to Keep Leading Zero in Excel Date Format****How to Remove Zeros in Front of a Number in Excel****[Solved]: Leading Zero Not Showing in Excel****Excel VBA to Format Number with Leading Zero****How to Write 00 in Excel**