Method 1 – Applying an Excel Formula with the SUBSTITUTE Function to Replace Text with Number
Steps:
- Select cell C4.
- Enter the following formula:
- 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:
- Press Enter and use AutoFill to fill the series.
How Does the Formula Work?
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:
- 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:
- Press Enter and use AutoFill to get the required output.
How Does the Formula Work?
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:
- 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?
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!