Excel Convert Number to Text with Leading Zeros: 10 Effective Ways

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.

Introduction of

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.

Download the Practice Workbook

Excel Convert Number to Text with Leading Zeros

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.

i. 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:

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

Using Text Format for Converting Number to Text with Leading Zeros

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

Using Text Format for Converting Number to Text with Leading Zeros

ii. Custom Format using Format Cells

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:

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

Custom Format using Format Cells for Converting Number to Text with Leading Zeros

Thereafter, you will get the following

Custom Format using Format Cells for Converting Number to Text with Leading Zeros

iii. Adding Apostrophe

Adding apostrophe 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 the cell.

Adding Apostrophe for Converting Number to Text with Leading Zeros

In the following dataset, if you type 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.

iv. TEXT Function

The Excel TEXT function converts numbers into text in a workingsheet

The syntax of the function is

=TEXT(value, format_text)

The arguments of the function are the following:

Value: the value you want to convert to text

Format_text: the formatting to apply

Steps:

  1. Select the blank cell e.g. D5
  2. 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.
  3. Press Enter

TEXT Function for Converting Number to Text with Leading Zeros

v. RIGHT Function

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

The syntax function is

=RIGHT(text,[num_chars]) 

The arguments of the function are the following:

Text: The text string containing the characters you want to extract.

Num_chars: Specifies the number of characters you want RIGHT to extract.

Steps:

  1. Select the blank cell e.g. D5
  2. 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
  3. Press Enter

RIGHT Function for Converting Number to Text with Leading Zeros

vi. BASE Function

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

The main syntax of the function is

=BASE(Number, Radix [Min_length]) 

The arguments of the function are the following:

Number: The number that you want to convert.

Radix: The base radix that you want to convert the number into.

Min_length: The minimum length of the returned string.

Steps:

  1. Select the blank cell e.g. D5
  2. 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.
  3. Press Enter

BASE Function for Converting Number to Text with Leading Zeros

vii. CONCATENATE function

This function joins a string of two or more texts. The syntax is

=CONCATENATE(text1, [text2], …)

The arguments of the function are the following:

text1: the first item to join

text2: the second item

Steps:

  1. Select the blank cell e.g. D5
  2. 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.
  3. Press Enter

CONCATENATE function for Converting Number to Text with Leading Zeros

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

viii. REPT and LEN function

number of times, REPT can repeat a text.

The syntax of REPT function is

=REPT(text, number_times) 

The arguments of the function are the following:

Text: The text you want to repeat.

Number_times: A positive number specifying the number of times to repeat text.

In addition, LEN returns the number of characters in a text string. The syntax is

=LEN(text)

Text    Required. The text whose length you want to find.

The whole process can be done in the following steps.

Steps:

  1. Select a blank cell e.g. E5
  2. Type the formula like =REPT(0,5) where 0 is the item to repeat and 5 is the times to repeat
  3. Press Ok

REPT and LEN function for Converting Number to Text with Leading Zeros

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

REPT and LEN function for Converting Number to Text with Leading Zeros

  1. Again you see the outputs are not padded (added leading zeros) that means all the outputs are not 5 digit text with leading zeros. That’s why we need to use the LEN function. For this,select the previous cell E5 or a blank cell
  2. Type the formula like =REPT(0,5-LEN(C5))&C5 where C5 is the number of ZIP codes.
  3. Press Ok

REPT and LEN function for Converting Number to Text with Leading Zeros

ix. Text.PadStart Function

Returns text of a specified length by padding the start of the given text.

The syntax of the function is

=Text.PadStart(text, count as number, character to pad) 

The arguments of the function are the following:

Text: the text you want to format (remember, it must in text format)

Count as number: The number of digits

Character to pad: The character you want to extract into the formatting

Steps:

  1. Select the cell range B4:B13
  2. Move into Data tab
  3. Choose the option From Table
  4. Check the selected table
  5. Press Ok

Text.PadStart Function for Converting Number to Text with Leading Zeros

Now we have to convert the ZIP code (number) into the text as the formula says.

  1. Pick the cursor to the upper left corner
  2. Select the Text option for transforming the number into the text

Text.PadStart Function for Converting Number to Text with Leading Zeros

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

  1. Click on the Add Column tab
  2. Select Custom Column command
  3. Write the new name of the column-like Text with Leading Zeros (Padded)
  4. 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
  5. Press Ok

Text.PadStart Function for Converting Number to Text with Leading Zeros

  1. Click on the Close & Load command
  2. Pick the Close & Load To option

Text.PadStart Function for Converting Number to Text with Leading Zeros

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

Text.PadStart Function for Converting Number to Text with Leading Zeros

The final output you’ll get like the following:

Text.PadStart Function for Converting Number to Text with Leading Zeros

x. Power Pivot

Power Pivot is an Excel add-in for powerful analysis and sophisticated data models. You can use this option.

Power Pivot can mash large numbers of data from different sources, quickly analyze information and easily share insights.

The function used here is FORMAT. The syntax of the function is

=FORMAT(value, format_text) 

The arguments of the function are the following:

value: The value you want to convert

format_text: The desired formatting you want to apply

Steps:

First, you have to enable the add-in Power Pivot. You can do this task by simply search Power Pivot in Excel and choose the option Enable Add-In. Please visit the link Start the Power Pivot add-in for Excel – Excel (microsoft.com) for a detailed guide.

  1. Select the cell range that you want to convert
  2. Click on Add to Data Model command from the Power Pivot tab

Power Pivot to Convert Number to Text with Leading Zeros

  1. Select a blank cell and type the formula like =FORMAT([ZIP Code (Number)], “00000”) where ZIP Code (Number) is the text to convert and “00000” is the formatting to apply

Power Pivot to Convert Number to Text with Leading Zeros

  1. Select the PivotTable option

Power Pivot to Convert Number to Text with Leading Zeros

  1. Pick Existing Worksheet 
  2. Fix the Location
  3. Select the cell C4
  4. Press Ok

Power Pivot to Convert Number to Text with Leading Zeros

  1. Select the Text with Leading Zeros (Padded)
  2. Move the Text with Leading Zeros (Padded) into Rows
  3. The output is at Row Labels

Power Pivot to Convert Number to Text with Leading Zeros

  1. Click the cursor on the Design tab
  2. Select the Report Layout
  3. Choose the Show in Tabular Form option

Power Pivot to Convert Number to Text with Leading Zeros

The final output of conversion of number to text is like the following-

Power Pivot to Convert Number to Text with Leading Zeros

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 zeros of a number on the other hand padded zeros are merged with the number forming a specified number of digits.

Conclusion

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.


Further Readings

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo