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

### Method 1 – Convert Numbers to the Text Format to Add Leading Zeroes in Excel

Steps:

• Insert an Apostrophe (‘) before starting a number to add 0 at the front. The Apostrophe will not show in the cell but will throw errors if you try to use the cell for calculations.

• Put an Apostrophe in all cells and add the necessary zeroes manually.

NOTES:

• Adding an Apostrophe at the front will convert the number to Text format. So, you can’t use them for any calculation,
• In the top left corner of the cells, you will see a green tiny arrow symbol. After clicking on the arrow, you’ll get a message “Number stored as text”. To continue as-is, click on the option “Ignore Error”
• Using this method will not automate the other cells.

Alternative Steps:

• Select the cells of the column.
• Go to the Number tab on the top ribbon.
• Open the drop-down menu.
• Select the Text option.

• The cells will be converted into the Text format.
• You can add necessary zeroes in front of numbers.

### Method 2 – Use Custom Formatting

Steps:

• Select the cells containing the numbers.
• Go to the Home tab ribbon and click the arrow in the bottom-right corner of the Number group.

• You’ll get a window named Format Cells.
• Stay in the Number tab and go to the Custom option.
• In the Type box, write 0000 to make a 4-digit number.
• Press the OK button.

• The Output column has been converted to four-digit numbers with leading zeroes.

NOTES:

• The numbers will remain in the Number format.
• If you copy and paste them as values, they’ll lose the formatting.
• In the formula bar, you will see the original values.
• You can control how many digits you need with the number of zeroes in the format.
• Numbers higher than the specified number of digits won’t be affected.

### Method 3 – Use the Ampersand (&) Symbol

Steps:

• Insert the following formula in cell C5:
`="00"&B5`
• This will add two zeroes in front of the number from cell B5.

• Copy the formula and paste it into other cells or use the Fill handle.

NOTES:

• This converts the values to text.
• The formula adds two leading zeroes to all values regardless of its existing length, so it won’t make them the same length.

### Method 4 – Utilize Excel Functions to Get Zeroes in Front of Numbers

#### Case 4.1 – Using the REPT Function

Syntax:

=REPT (text, number_times)

Text: The text or character that will be repeated

Number_times: The number of times to repeat the text.

Steps:

• Input this formula in cell C5.
`=REPT(0,2)&B5`

• Copy and paste the formula into the other cells.

Let’s modify the formula to make all results at least six characters long.

• Insert this formula into cell C5.
`=IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5)`

Formula Explanation:

• LEN(B5): Calculates the number of characters in cell B5
• LEN(B5)<6: The condition that if the number of characters in cell B5 is less than 6
• 6-LEN(B5): Returns the value that is needed to make the number get 6 digits.
• REPT(0,6-LEN(B5))&B5: Adds the required zeroes in front with cell B5
• =IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5): Wraps the formula in an IF function to ignore numbers that are already six digits long or longer.
• Copy and paste the formula to other cells using the Fill Handle icon or shortcuts.

NOTES:

• As the cells remain in Number format, you can use them for any calculations.

#### Case 4.2 – Using the TEXT Function

Syntax

=Text(Value, format_text)

Value: Value that you will convert to text

Format_text: Format that you want to use.

Steps:

• Insert this formula into cell C5
`=TEXT(B5,"0000")`
• This will convert the value of cell B5 to Text format and will add the necessary zeroes to make it 4 digits.

• Copy and paste the formula to other cells using the Fill Handle icon or shortcuts Ctrl + C and Ctrl + V.

#### Case 4.3 – Using the CONCAT or CONCATENATE Function

Steps:

• Insert the following formula in the cell C5:
`=CONCAT("00",B5)`

• Use the Fill Handle icon to copy and paste the formula to the other cells.

NOTE:

This will just add two zeroes to all cells.

#### Case 4.4 – Using the RIGHT Function

Syntax

=RIGHT (text, [num_chars])

Text: Value that is being extracted from.

Num_chars: Number of characters that will be extracted from the right. Omitting this returns the last character only.

Steps:

• Insert the following formula in cell C5.
`=RIGHT("00"&B5,4)`

• Use the Fill Handle icon to copy and paste the formula to the other cells.

This formula will trim numbers that have more than four digits and will return a three-digit number for a single-digit input.

#### Case 4.5 – Using the BASE Function

Syntax:

=BASE (number, radix, [min_length])

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

Radix: Base radix to which the number will convert. Must be an integer >=2 or <=36.

Min_length: The minimum length of the string

Steps:

• Insert the following formula in cell C5.
`=BASE(B5,10,4)`

• Use the Fill Handle icon to copy and paste the formula to the other cells.

Read More: How to Add Leading Zeros in Excel

### Method 5 – Use the Power Query Tool

• Go to the Data tab, select Get Data, and choose Launch Power Query Editor.

• You’ll get a window named Power Query Editor.
• Go to the Home tab, click on the New Source drop-down menu, and select Excel Workbook.
• Select a workbook and extract data from it.

• Select the option Enter Data and input data manually.

• Make a column by pasting the data from the workbook or inputting data manually.
• Press OK.

• Go to the Add Column tab and choose the Custom Column option.

• You’ll get a Custom Column window.
• Name the column.
• Insert the formula given below:

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

• Press OK.

• You will get a column with the name you chose where the cells have at least four digits.
• Select Close & Load.

• You’ll get a table in a new sheet with the results.

## Things to Remember

• Using an Apostrophe at the front will convert the number to Text format and you have to add zeroes in front manually.
• A Custom Format is the most useful option to add zeroes and make numbers of the same digits.
• Formatting numbers to Text will allow you to add zeroes in front but hampers calculations.
• The REPT function will add the specified number of zeroes no matter the number of digits. Use the REPT, LEN, and IF functions together for better results.
• The TEXT function effectively applies a Custom Format.
• The CONCAT function will also add the given amount of zeroes no matter the number of digits.
• The RIGHT and BASE functions make the numbers have the same number of digits.
• Power Query will help to extract data and format them from external sources.

Download the Practice Workbook

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF