## Method 1 – Using Text Format to Convert a Number in Excel with Leading Zeros

**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. - Choose the Text option from the lists.

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

## Method 2 – Applying Custom Format to Convert Number to Text with Leading Zero in Excel

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

** **

- You will get the following result.

## Method 3 – Adding Apostrophe Before Number to Convert into Text with Leading Zeros

You can add apostrophes to convert numbers to text with leading zeros. The main advantage is that the apostrophe isn’t visible in the cell, though it is available in the formula bar and can be edited.

** **

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.

## Method 4 – Applying Excel TEXT Function to Convert Number to Text with Leading Zeros

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

## Method 5 – Inserting Excel RIGHT Function to Convert Number into Text

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

## Method 6 – Using Excel BASE Function to Include Leading Zeros

**Steps**

- Select the blank cell e.g.
**D5**. - Type the formula like:

`=BASE(C5,10,5)`

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

- Press
**Enter**.

## Method 7 – Applying CONCATENATE Function to Add Leading Zero in Excel

**Steps**

- Select the blank cell e.g.
**D5**. - Type the formula like:

`=CONCATENATE("00", C5)`

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

- Press
**Enter**to apply the formula.

- Drag the
**Fill Handle**icon down the column.

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

## Method 8 – Joining REPT and LEN Functions to Convert Number to Text

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

- 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`

**C5** is the number of ZIP codes.

- Press
**OK**.

## Method 9 – Using Power Query to Convert Number to Text with Leading Zeros

**Steps**

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

- 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

We use the function and 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")`

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 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 the following.

## How to Convert Number to Text Automatically in Excel

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**

- You need to select the range of cells
**C5**to**C13**.

- Go to the
**Home**tab on the ribbon. - From the
**Number**section, select the down arrow.

- 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, errors will appear to inform us that they are numbers but are stored in text format.

- To eliminate the error, select the range of cells
**C5**to**C13**. - Select the down arrow.
- Click on
**Ignore Error**.

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

## Related Articles

- How to Convert Number to Text for VLOOKUP in Excel
- How to Convert Number to Text without Scientific Notation in Excel
- How to Convert Number to Text and Keep Trailing Zeros in Excel
- Convert Number to Text with Commas in Excel

**<< Go Back to Excel Convert Number to Text | Learn Excel**