How to Put 0 in Excel in Front of Numbers (5 Handy Methods)

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.


Download Practice Workbook

You can download the practice workbook from here:


5 Effective Methods to Put 0 in Excel in Front of Numbers

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.

How to Put 0 in Excel in Front of Numbers

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

How to Put 0 in Excel in Front of Numbers

💬 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.

How to Put 0 in Excel in Front of Numbers

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

How to Put 0 in Excel 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 (10 Ways)


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.

How to Put 0 in Excel in Front of Numbers

  • 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.

How to Put 0 in Excel in Front of Numbers

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

How to Put 0 in Excel in Front of Numbers

💬 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 (10 Ways)


Similar Readings


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.

Using Ampersand (&) Symbol

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

Using Ampersand (&) Symbol

💬 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.

Read More: Excel Convert Number to Text with Leading Zeros: 10 Effective Ways


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

 Using REPT Function 

  • 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.

  Using REPT Function 

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)

  Using REPT Function 

🔎 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.

  Using REPT Function 

💬 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 Text 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 Text format and will add the necessary 0’s to make it 4 digits.

Using TEXT Function

  • Then, copy and paste the formula to other cells using the fill handle icon or shortcuts Ctrl + C and Ctrl + V.

Using TEXT Function


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)

Using CONCAT (or CONCATENATE) Function

  • Then, use the fill handle icon to copy and paste the formula to the other cells.

Using CONCAT (or CONCATENATE) Function

💬 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.

Read More: How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)


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)

Using RIGHT Function

  • Then, use the fill handle icon to copy and paste the formula to the other cells.

Using RIGHT Function


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)

Using BASE Function

  • Then, use the fill handle icon to copy and paste the formula to the other cells.

How to Put 0 in Excel in Front of Numbers

Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)


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.

Power Query and Text.PadStart Function

  • 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.

Power Query and Text.PadStart Function

  • 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.

Power Query and Text.PadStart Function

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

Power Query and Text.PadStart Function

  • 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.

Power Query and Text.PadStart Function

  • 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.

How to Put 0 in Excel in Front of Numbers

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

How to Put 0 in Excel in Front of Numbers


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.

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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo