How to Convert Number to Text with Leading Zeros in Excel

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.

Using Apostrophe - convert number to text with leading zeros

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

Text format - convert number to text with leading zeros

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

Special format - convert number to text with leading zeros

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

Custom format - convert number to text with leading zeros

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

BASE function - convert number to text with leading zeros

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

TEXT function - convert number to text with leading zeros

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

RIGHT function - convert number to text with leading zeros

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

REPT -LEN Functions - convert number to text with leading zeros

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

CONCATENATE function - convert number to text with leading zeros

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


Related Articles

(Visited 51 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo