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

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

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

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. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.