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

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

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

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

### 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**T****ext**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
**F****ill 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**