# Excel Formula to Replace Text with Number (5 Examples)

We work with texts and numbers in our Excel worksheet all the time. And sometimes, we may want to exchange the texts with numbers for various reasons. In this article, we will show you the suitable examples of applying an Excel Formula to Replace Text with Number.

## Excel Formula to Replace Text with Number: 5 Suitable Examples

To illustrate, weâ€™ll use the following dataset as an example. For instance, the below dataset has a text â€˜toâ€™ in the B4:B8 array. Here, weâ€™ll replace it with the number â€˜2â€™.

### 1. Excel Formula with SUBSTITUTE Function to Replace Text with Number

In our first example, weâ€™ll use the SUBSTITUTE function. This function replaces an old text in a text string with a new text as specified in the argument section. Therefore, follow the steps below to perform the task.

STEPS:

• First, select cell C4. Here, type the formula:
`=SUBSTITUTE(B4," to"," 2")`
• Then, press Enter. Thus, itâ€™ll return the desired result.

NOTE: The SUBSTITUTE function looks for â€˜toâ€™ (old text) in cell B4 and replaces it with â€˜2â€™ (new text).
• Lastly, use AutoFill to complete the changes in the following cells.

### 2. Insert Nested SUBSTITUTE Formula for Replacing Text

However, if we have more than one text in a single text string that we want to change, we can apply the nested SUBSTITUTE formula. Nested formula means using the SUBSTITUTE function more than once to create the formula. In the below dataset, we want to exchange â€˜oneâ€™ and â€˜toâ€™ with â€˜1â€™ and â€˜2â€™ respectively. So, learn the following steps for creating Excel Formula to Replace Text with a Number.

STEPS:

• Firstly, select cell C4 to type the formula:
`=SUBSTITUTE(SUBSTITUTE(B4,"One","1"),"to","2")`
• Next, press Enter and use AutoFill to fill the series.

🔎 How Does the Formula Work?

• SUBSTITUTE(B4,”One”,”1″)

The SUBSTITUTE function replaces â€˜Oneâ€™ with â€˜1â€™ in cell B4.

• SUBSTITUTE(SUBSTITUTE(B4,”One”,”1″),”to”,”2″)

Finally, this SUBSTITUTE formula replaces â€˜toâ€™ with â€˜2â€™ in the output of SUBSTITUTE(B4,”One”,”1″).

### 3. Apply Excel REPLACE Function for Substituting Text with Number

Moreover, we can use another Excel function called REPLACE function for substituting text with a number. This function looks for the starting position and the number of characters from that position in a text string. And then, change the characters with a specified new one. Hence, follow the below process to carry out the operation.

STEPS:

• First of all, in cell C4, type the formula:
`=REPLACE(B4,6,2,"2")`
• At last, press Enter to make certain changes.

NOTE: You canâ€™t use AutoFill in this case as the starting position is different for different cells.

### 4. Substitute Text Using Excel Formula with REPLACE and FIND Functions

In the previous example, we couldnâ€™t use AutoFill as the starting position is different in each cell. To get rid of the shortcoming, we can introduce the FIND function. This function searches for a specific text in a text spring. Now, learn the following process for applying Excel Formula to Replace Text with Number.

STEPS:

• Select cell C4 at first. Type the formula:
`=REPLACE(B4, FIND("to",B4), 2, "2")`
• Now, press Enter and use AutoFill to get the required output.

🔎 How Does the Formula Work?

• FIND(“to”,B4)

The FIND function seeks the starting position of â€˜toâ€™ in cell B4 and returns 6.

• REPLACE(B4, FIND(“to”,B4), 2, “2”)

Lastly, the REPLACE formula substitutes â€˜toâ€™ with â€˜2â€™.

### 5. Nested REPLACE Formula to Exchange Text with Number

Additionally, we can create a nested REPLACE formula if we have more than a single text to substitute. Therefore, follow the procedure to perform the task.

STEPS:

• Firstly, select cell C4 and type the formula:
`=REPLACE(REPLACE(B4,10,2,"2"),1,3,"1")`
• Then, press Enter. Itâ€™ll return the value performing the changes.

NOTE: As the starting position doesnâ€™t match in every cell, we canâ€™t use AutoFill.

🔎 How Does the Formula Work?

• REPLACE(B4,10,2,”2″)

The REPLACE function substitutes â€˜toâ€™ in cell B4 with â€˜2â€™.

• REPLACE(REPLACE(B4,10,2,”2″),1,3,”1″)

Lastly, this REPLACE formula substitutes â€˜Oneâ€™ with â€˜1â€™ in the output of REPLACE(B4,10,2,”2″).

## Replace Text with Number without Using Excel Formula

We can also use Excel â€˜Find and Replaceâ€™ feature for exchanging text with a number. Hence, learn the steps to carry out the operation.

STEPS:

• Select cell B4 at first.

• Then, press the keys Ctrl and HÂ together.
• As a result, the â€˜Find and Replaceâ€™ dialog box will pop out.
• Type to in the Find what box and type 2 in the Replace withÂ box.
• After that, press Replace All.

• At last, itâ€™ll return the desired output as shown in the following picture.

## Conclusion

Henceforth, you will be able to apply Excel Formula to Replace Text with Number following the above-described Examples. Keep using them and let us know if you have any more ways to do the task.

Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Excel REPLACE Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelorâ€™s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF