Method 1 – Applying an Excel Formula with the SUBSTITUTE Function to Replace Text with Number
Steps:
- Select cell C4.
- Enter the following formula:
=SUBSTITUTE(B4," to"," 2")- Press Enter. It’ll return the desired result.
- Use AutoFill to complete the changes in the following cells.
Method 2 – Inserting the Nested SUBSTITUTE Formula for Replacing Text
Steps:
- Select cell C4.
- Enter the following formula:
=SUBSTITUTE(SUBSTITUTE(B4,"One","1"),"to","2")- 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″).
Read More: How to Replace Text in Selected Cells in Excel
Method 3 – Applying the Excel REPLACE Function to Substitute Text with Number
Steps:
- Select cell C4.
- Enter the following formula:
=REPLACE(B4,6,2,"2")- Press Enter to make the changes.
NOTE: You can’t use AutoFill in this case, as the starting position is different for different cells.
Read More: How to Replace Text between Two Characters in Excel
Method 4 – Using an Excel Formula with REPLACE and FIND Functions
Steps:
- Select cell C4.
- Enter the following formula:
=REPLACE(B4, FIND("to",B4), 2, "2")- 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’.
Read More: How to Replace Text after Specific Character in Excel
Method 5 – Applying the Nested REPLACE Formula to Exchange Text with Number
Steps:
- Select cell C4.
- Enter the following formula:
=REPLACE(REPLACE(B4,10,2,"2"),1,3,"1")- Press Enter. It’ll return 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 a Number without Using an Excel Formula
Steps:
- Select cell B4.
- 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.
- Press Replace All.
- It’ll return the desired output as shown in the following picture.
Download the Practice Workbook
Download the following workbook to practice.
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
