How to Substitute Multiple Characters in Excel: 7 Methods

Method 1 – Using the SUBSTITUTE Function to Substitute Multiple Characters

Below is a dataset of Microsoft Word version names. We want to substitute “Word” with “Excel”. We use the SUBSTITUTE function to do so.

Step 1:

• Enter the following formula in a cell:

Step 2:

• Press Enter.

Step 3:

• Repeat the previous steps for the other two criteria.

You will get values for all subsequent fields.

Method 2 – Nesting the SUBSTITUTE Function to Substitute Multiple Characters

Below, we will substitute the three codes with full names.

Step 1:

• Enter the following formula in cell C5:

Step 2:

• Press Enter.

Step 3:

• Paste the formula in the other cells.

Method 3 – Performing the SUBSTITUTE Function Using the INDEX Function to Substitute Multiple Characters

The INDEX function is used to replace pairs from another table. Here, we will substitute red and blue with green and white.

Step 1:

• Enter the following formula in cell C5:

• INDEX find range is E5:E6

Step 2:

• Press Enter.

• Copy the formula in other cells.

Method 4 – Applying the REPLACE Function to Substitute Multiple Characters

Below, we will substitute ‘Face’ for ‘Fact’ using the Replace Function.

Step 1:

• Enter the following formula in cell D5:

Step 2:

• Press Enter.

Step 3:

• Copy the formulas for the required cells.

Method 5 – Nesting the REPLACE Function to Substitute Multiple Characters

Below, we have a list of phone numbers in column A that are formatted: 123-456-789. We want to change them to: 123 456 789. To do that, we use the Replace Function.

Step 1:

• Enter the following formula in cell C5:

Step 2:

• Press Enter.

Step 3:

• Copy the formula and repeat the steps for the required cells.

Method 6 – Combining Multiple Functions to Substitute Multiple Characters

Below, the Reduce Function shows the value in the cell range C5:C7. If the criteria match, it substitutes the value in column B and adjusts column C.

Step 1:

• Enter the following formula in cell D5:

Step 2:

• Copy the formula to cell D7.

Method 7 – Run VBA Code to Substitute Multiple Characters

Below, we will run a VBA code to substitute multiple characters. We have used the same phone number example as Method 5.

To run a VBA code to substitute multiple characters, just follow the steps described below.

Step 1:

• Press Alt + F11 to open the Macro-Enabled Worksheet.
• Go to the Insert tab.
• Select Module.

Step 2:

• Past the following VBA code into the program window:

Sub replaceAll()

'declare object variable to hold reference to cell you work with
Dim myCell As Range

'declare variables to hold parameters for string replacement (string to replace and replacement string)
Dim myStringToReplace As String
Dim myReplacementString As String

'identify cell you work with
Set myCell = ThisWorkbook.Worksheets("VBA").Range("C5")
'specify parameters for string replacement (string to replace and replacement string)
myStringToReplace = "234-235-5689"
myReplacementString = "234 235 5689"

'replace all occurrences within string in cell you work with, and assign resulting string to Range.
'Value property of cell you work with
myCell.Value = Replace(Expression:=myCell.Value, 
Find:=myStringToReplace, Replace:=myReplacementString)

End Sub

• Press Enter.

Download Practice Workbook

Download this practice workbook to the exercises.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

Stay with us & keep learning.

Related Articles

• How to Find and Replace Asterisk (*) Character in Excel
• Find and Replace Tab Character in Excel
• [Fixed!] Excel Find and Replace Not Working
• How to Find and Replace in Excel Column
• Find And Replace Multiple Values in Excel
• How to Replace Special Characters in Excel

<< Go Back to Find and Replace | Learn Excel