Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Convert Number to Text with Leading Zeros in Excel

Leading zeros aid predominantly to specify the length of a number. In the case of maintaining records such as any special identity number, zip code, security numbers, etc., you have to keep leading zeros on cells. However, when you try to enter a zip code like ‘00901’ of Puerto Rico in a cell, Excel truncates it to ‘901′ right away. It’s extremely important for Microsoft Excel to deal with leading zeros. For this, you have to convert the number to text. In this article, I am going to discuss the ways of converting numbers to text with leading zeros. This article will show how to convert number to text with leading zeros in Excel.


Download Practice Workbook

Download the practice workbook below.


9 Easy Methods to Convert Number to Text with Leading Zeros in Excel

Let me introduce the dataset first. Actually, nine postcodes are given without leading zeros, and good to say that the postcode consists of five digits. Now, we have to add leading zeros and we may proceed with the following effective ways. We have found 9 effective methods to convert number to text with leading zeros in Excel.


1. Using Text Format

It’s a simple solution

You can add leading zero without any problems if you change the cell format from number to text. This means Excel does not treat your number as an actual number and does not remove zeros. So how can I do that? Just follow the steps

Steps

  • Select a blank cell or cell range where you want to use the format.
  • Click on the Format Dropdown selection in the Numbers command from the Home tab.
  • Chose the Text option from the lists.

Using Text Format to Convert Number to Text with Leading Zeros in Excel

  • Now, the cells are ready to show leading zeros. After inputting the postcode manually, we will get the following output.

Read More: How to Convert Numbers to Texts/Words in Excel


2. Utilizing Custom Format

Custom Format is a special type of format where you have numerous options to select in Format Cells. We will pick one from them.

Steps

  • Copy the ZIP codes to a new cell range and select the cell range.
  • Click on the Number command.
  • Choose the Custom option from Format Cells.
  • Type ‘00000’ as the zip code consists of five digits.
  • Press OK.

Utilizing Custom Format to Convert Number to Text with Leading Zeros in Excel

  • Thereafter, you will get the following result.

Read More: [Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)


3. Adding Apostrophe Before Number

Adding apostrophes is the easiest way to keep the leading zeros before any number. The main advantage is that the apostrophe isn’t visible in the cell though it is available in the formula bar and you can edit it.

Adding Apostrophe Before Number to Convert Number to Text with Leading Zeros in Excel

In the following dataset, if you type an apostrophe (‘) character before inputting the ZIP code and press Enter (that means you have to type ‘04330 in lieu of just 04330), you’ll see the ZIP code with leading zeros.

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


4. Applying TEXT Function

The TEXT function helps to convert numbers into text in a working sheet. To understand the process, follow the steps carefully.

Steps

  • Select the blank cell e.g. D5.
  • Type the formula like:
=TEXT(C5, "00000")

where C5 is the value of a ZIP code and “00000” is the desired formatting as the ZIP code number consists of five digits.

  • Press Enter.

Applying TEXT Function to Convert Number to Text with Leading Zeros in Excel

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


5. Using RIGHT Function

The RIGHT function returns all characters in a text string, depending on the number of characters you specify. Follow the steps carefully.

Steps

  • Select the blank cell e.g. D5.
  • Type the formula like:
=RIGHT("00000"&C5,5)

where C5 is the value of a ZIP Code, “00000” is the desired formatting and 5 is the number of  characters

  • Press Enter.

Using RIGHT Function to Convert Number to Text with Leading Zeros in Excel


6. Use of BASE Function

The BASE function converts a number into a text representation with the given radix (base).

To understand the method properly, follow the steps.

Steps

  • Select the blank cell e.g. D5.
  • Type the formula like:
=BASE(C5,10,5)

where C5 is the number of a ZIP code, 10 is the base, and 5 is the desired length of characters.

  • Press Enter.

Use of BASE Function to Convert Number to Text with Leading Zeros in Excel


7. Applying CONCATENATE Function

The CONCATENATE function joins a string of two or more texts. We can use it to convert numbers to text with leading zeros. To do this method, follow the steps carefully.

Steps

  • Select the blank cell e.g. D5.
  • Type the formula like:
=CONCATENATE("00", C5)

where “00” is the first item & C5 is the second item, mainly the number of a ZIP code.

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

Applying CONCATENATE Function to Convert Number to Text with Leading Zeros in Excel

Note: Using CONCATENATE function you can add leading zero but you can not pad leading zeros.

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


8. Combining REPT and LEN Functions

A number of times, the REPT function can repeat a text. In addition, the LEN function returns the number of characters in a text string. The combination of REPT and LEN functions can convert numbers to text with leading zeros. To understand the method clearly, follow the steps.

Steps

  • Select a blank cell e.g. E5.
  • Type the formula like:
 =REPT(0,5)

where 0 is the item to repeat and 5 is the time to repeat

  • Press OK.

  • You see that all values are zeros. So that we have to merge Column C and Column D. For this, select the cell E5 or a new blank cell in a new worksheet.
  • Type the formula like:
=REPT(0,5)&C5
  • Press OK.

  • Again you see the outputs are not padded (added leading zeros) which means all the outputs are not 5-digit text with leading zeros. That’s why we need to use the LEN For this, select the previous cell E5 or a blank cell
  • Type the formula like:
=REPT(0,5-LEN(C5))&C5

where C5 is the number of ZIP codes.

  • Press OK.

Combining REPT and LEN Functions to Convert Number to Text with Leading Zeros in Excel

Read More: How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)


9. Using Power Query Editor

The Text.PadStart function Returns the text of a specified length by padding the start of the given text. We can convert numbers to text with leading zeros by using this function. To understand the process, follow the steps.

Steps

  • Select the cell range B4:B13.
  • Move into the Data tab.
  • Choose the option From Table.
  • Check the selected table.
  • Press OK.

Using Power Query Editor to Convert Number to Text with Leading Zeros in Excel

  • Now we have to convert the ZIP code (number) into the text as the formula says.
  • Pick the cursor to the upper left corner
  • Select the Text option for transforming the number into the text

At this moment, we have to use the function. Thus, we need to add a new column.

  • Click on the Add Column tab.
  • Select Custom Column.
  • Write the new name of the column-like Text with Leading Zeros (Padded).
  • Type the formula like:
=Text.PadStart([#" ZIP Code (Number)"],5,"0")

where the column namely ZIP Code (Number) is input as text, 5 is the number of digits & 0 is the character to pad.

  • Press OK.

  • Click on the Close & Load command.
  • Pick the Close & Load To option.

  • Choose the loading option as Table.
  • Select cell C4 of the Existing Working Sheet.
  • Press Load.

  • The final output you’ll get is like the following.

Utilizing Power Query Editor to Convert Number to Text with Leading Zeros in Excel


How to Convert Number to Text Automatically in Excel

In order to convert numbers to text automatically in Excel, we can easily use the format cell. Here, we need to convert the range of cells into text. After that, if you enter any number, it will act as text automatically. Follow the steps carefully.

Steps

  • First, you need to select the range of cells C5 to C13.

  • Then, go to the Home tab on the ribbon.
  • From the Number section, select the down arrow.

How to Convert Number to Text Automatically in Excel

  • Now, if you put numbers in that range of cells, it will act as text automatically.
  • As we enter numbers that are not text values, it will show errors to inform you that they are numbers but are stored in text format.

  • To eliminate the error, select the range of cells C5 to C13.
  • Then, select the down arrow.
  • After that, click on Ignore Error.

  • As a result, we get the following result which eliminates the errors.


💬 Things to Keep in Mind

Be sure that the data is in text format before using the Text.PadStart function. Besides, check the requirement whether you have to find added leading zeros or padded leading zeros.

As added leading zeros are just the added prefix before the zeros of a number on the other hand padded zeros are merged with the number forming a specified number of digits.


Conclusion

We have shown 9 effective methods to convert number to text with leading zeros in Excel. Though all the ways discussed above are effective, it also depends on your requirements. So, choose the best one for you and your dataset. And please share your thoughts in the comments section below. Thanks for being with me. Don’t forget to visit our Exceldemy page.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo