Excel is a famous software to process many kinds of information into impactful action. Inserting various types of data, leading zeros in a cell is often omitted. If they are not converted to text, Excel does that. Because Excel considers the number as an integer and leading zeros as unnecessary data to store. Although the method is convenient for data storing, users usually input leading zeros in a cell to present IDs, codes, account numbers, and many others. Regular life digits like these prove this auto removal of leading zeros harmful. This article will guide you through ten methods to convert number to text with leading zeros in Excel and mitigate this issue. You will get step-by-step instructions, with a figurative description for all the methods.

## 📁 Download Excel File

Download the Excel file below.

## Learn to Convert Number to Text with Leading Zeros in Excel Using These Nine Methods

The article is going to introduce nine methods to convert number to text in Excel with leading zeros. The methods will provide simple to complex solutions gradually. A simple solution involves the addition of an **Apostrophe** or changing the **Format** to **Text**, **Special**, or **Custom**. Complex ones will be covering **functions** such as **BASE**, **TEXT**, **RIGHT**, **REPT** – **LEN**, and **CONCATENATE**. Users will get this versatile walkthrough of these solutions to one of the fundamental problems.

**Approach**

### 1. Using Apostrophe

The easiest method to convert number to text with leading zeros in Excel is using **Apostrophe**. On the other hand, it is inconvenient to use in case of a wide range of records.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, you need to select the cell where you will add the leading zero(s). Before starting to input, you should use
**Apostrophe**, which is the**(‘)**character on your keyboard. - Now, you need to add the number along with the leading zeros just as the number is addressed.

- Next, Excel will show you an error sign, where you should select
**Ignore Error**.

- You have to repeat the process manually for every cell in the worksheet.

**📕 Read More: 4 Ways to Convert Number to Text and Keep Trailing Zeros in Excel**

**Approach**

### 2. Utilizing Text Format

You can declare column(s) to show data in **Text format**. Keeping numbers, with leading zero(s) at **Number** or **General** format, will automatically delete leading zeros, right after inserting the number.

⬇️⬇️ **STEPS **⬇️⬇️

- To start with the method, you need to format the column (or row or cells) from
**General**to**Text**from the number format tool under the**Home**tab.

- After that, you can write numbers with as much as leading zeros as needed. Excel will not remove the zeros.

**Approach**

### 3. Applying Special Format

The** special format** is a go-to method for formatting ZIP codes, phone numbers, social security numbers and many other country-based numerical formats. This option allows as many as leading zeros for numbers. You don’t need to enable it cell by cell.

⬇️⬇️ **STEPS **⬇️⬇️

- The number format tool has an extension. As shown in the picture, you need to select a column (or rows or cells). Along with that, you will get the number format dialogue box from the extension option.
- You should start this step by choosing the
**Special**Followed by the category, you can choose your location to get available special formats. As portrayed in the picture, we have chosen**English (United States)**which gets us to select**Social Security Number**as the data type.

- After finalizing the choice, Excel will format the selected region. Eventually, you can see all the data in the format of the United States Social Security Number when you call the data from previous cells.

**Approach**

### 4. Employing Custom Format

You can add as many leading zeros using the **Custom format**, declaring the digit allocation for a cell or cells or rows or columns. This command will set the trailing digits with the non-zero values and complete the remaining leading digits with zero(s).

⬇️⬇️ **STEPS **⬇️⬇️

- Similar to previous format methods, the number format dialogue box is also the start of this method.
- You should select the Custom category. As the picture shows, you will need to
**Type**zeros in the number that you want all your numbers to show.

- Completing the choice, the entire selected region will be formatted. You can include all by the following formula.

**=C6**

- Copy it through the column.

**Approach**

### 5. Using BASE Function

In the case of converting numerical data to text with leading zeros, we can make use of **the BASE function** as well. The **BASE **function has radix 2, 7, 10, and 16 for binary, octal, decimal, and hexadecimal numbers respectively. Declaring a function with the previous cell ID, radix, and the digits you would like to view your number with.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, we need to select the cell where you will store the corrected data. There, you can use the following function.

**=BASE(C6,10,8)**

- As shown in the image below, you can see the function will get your desired output.

- In the second step, we need to copy the formula of
**E6**and drag the action to**E8**. You should get a visual like the following with this method.

**📕 Read More: How to Convert Number to Text with 2 Decimal Places in Excel**

**Approach**

### 6. Using TEXT Function

In the case of converting numerical data to text with leading zeros, you can try **the TEXT function**. You should declare the **TEXT** function with the previous cell ID and zeros under inverted commas as much as the digits you would like to view your number with.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, you should select the cell where you will store the corrected data. You can use the following function there where
**C6**is the reference cell and required digits are presented by eight zeros.

**=TEXT(C6,“00000000”)**

- As shown in the image below, you can see the function will get your desired output.

- In the second step, we need to copy the formula of
**E6**and drag the action to**E8**. You should get a visual like the following with this method.

**📕 Read More: 3 Ways to Convert Number to Text with Commas in Excel**

**Approach**

### 7. Employing RIGHT Function

**The RIGHT function** is a digit setting function. You can set the data format or digit allocation, followed by mentioning the exact number.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, you will select the cell where you will store the corrected data. You need to write the following function in the cell.

**=RIGHT(“00000000”&C6,8)**

- As shown in the image below, you can see the function will get your desired output whereas “00000000”&C6 is the data format and 8 is the digit count.

- In the second step, you should copy the formula of
**E6**and drag the action till**E8**. You should get a visual like the following with this method.

**Approach**

### 8. Merging REPT and LEN Functions

**The REPT function** is used to repeat a string multiple times. **The LEN function** returns the number of total characters in a text string. Using the **REPT** function on the **LEN** function returns the repeated action on the number that **LEN **returns.

⬇️⬇️ **STEPS **⬇️⬇️

- To begin with, you need to select the cell where you will store the corrected data. You can use the following function to correct the number from
**C6**

**=REPT(0,8-LEN(C6))&C6**

- As shown in the image below, you can see the function will get your desired output. Here
**LEN**is selected on**C6**. Upon the return on character number of**C6**, it is subtracted from 8, so that zero can be repeated as many times as the leading digits from the subtraction.

- In the second step, we need to copy the formula of
**E6**and drag the action to**E8**. You should get a visual like the following with this method.

**Approach**

### 9. Employing CONCATENATE Function

In order to convert numerical data to text with leading zeros, we can make use of **the CONCATENATE function** as well. It joins two text strings from different cells.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, you should select the cell where you will store the corrected data. There. You can follow the function below

**=CONCATENATE(“00”,C6)**

- As shown in the image below, you can see that the function will get your desired output.

- In the second step, we need to copy the formula of
**E6**and drag the action to**E8**. You should get a visual like the following with this method.

- As you can notice, unlike other methods,
**CONCATENATE**doesn’t maintain any specific digit count, and only adds strings as commanded. Moreover, it should be avoided if the data range isn’t of the same character length.

## 📝 Takeaways from This Article

The article allows the readers to understand the varieties of options available to convert numbers to text with leading zeros.

📌 Using apostrophe before numbers with leading zeros is a common practice.

📌 You can get an entire selection to show number as the way written. For that, changing the **Number** format to the **Text** format is the easiest.

📌 **Special** format ensures the exact format of the chosen entity. It can be code, phone numbers, etc.

📌 **Custom** format is applicable when the template isn’t available and a specific digit count is set.

📌 By the **BASE** function, you can arrange the dataset as a specific numerical system. It comes with a specific digit count.

📌 **TEXT** function encloses cell data to a declared digit count.

📌 **RIGHT** function is used to command data from the trailing side. Setting the provided data on the trailing, the **RIGHT** function can fill the remaining with zeros.

📌 **REPT** function and **LEN** function combination is one of the complex methods. However, it returns the desired result with precision.

📌 **CONCATENATE** helps to join a specific amount of leading zeros with another data string. Although easy and effective, it might create a misconception about the data

## Conclusion

To convert numbers to text with leading zeros, Apostrophe, format changes to **Text**, **Special**, **Custom,** or application of functions such as **BASE**, **TEXT**, **RIGHT**, **REPT**–**LEN**, and **CONCATENATE **can be used as per the article. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow **ExcelDen** to get more access to solutions regarding your Excel problems.