How to Substitute Multiple Characters in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

While performing repetitive works on a large scale, you may need to substitute multiple characters or values at a time. This tutorial takes a deeper look at how to substitute multiple characters in Excel based on their location with another based on content. We will apply several functions and Visual Basic Application code to achieve this task.


Substitute Multiple Characters: 6 Suitable Ways

1. Use the SUBSTITUTE Function to Substitute Multiple Characters

In Excel, the SUBSTITUTE Function substitutes one or more instances of a specified character or text string with another character (s). In the screenshot below, here is a data set of Microsoft Word version names. For instance, we want to substitute “Word” with “Excel”. We will apply the SUBSTITUTE function to get it done.

Use the SUBSTITUTE Function to Substitute Multiple Characters

The syntax of the Excel SUBSTITUTE function is as follows:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Text – the original text in which you’d like to make changes.

Old_text – the characters you’d want to substitute off.

New_text – the new characters to use in place of old text

Instance_num – the incidence of the old text that you’d like to replace. If this parameter is left blank, every instance of the old text will be replaced with the new content.

For instance, all of the formulas below substitute “1” with “2” in cell B5, but the results vary based on the number you provide in the last argument:

a) =SUBSTITUTE(B5, “Word”, “Excel”, 1) – Replaces the first incidence of “Word” with “Excel”.

b) =SUBSTITUTE(B5, “Word”, “Excel”, 2) – Replaces the second incidence of “Word” with “Excel”.

c) =SUBSTITUTE(B5, “Word”, “Excel”) – Replaces all incidences of “Word” with “Excel”.

In the screenshot below, we have shown an example of the first occurrence. To do so, simply follow the steps.

Step 1:

  • Type the following formula in a cell,
=SUBSTITUTE(B5,"Word","Excel",1)

Use the SUBSTITUTE Function to Substitute Multiple Characters

Step 2:

  • Press Enter to see the results.

Use the SUBSTITUTE Function to Substitute Multiple Characters

Step 3:

  • Repeat the previous steps for the other two criteria.

Use the SUBSTITUTE Function to Substitute Multiple Characters

As a result, you will obtain values for the first, second, and all occurrences consecutively to substitute multiple characters in Excel.

Note. Remember that the SUBSTITUTE function is case-sensitive. Make sure to enter uppercase and lowercase perfectly. As you see in the below image, lowercase Excel could not find the values. So, no substitution occurred.

Use the SUBSTITUTE Function to Substitute Multiple Characters


2. Nest the SUBSTITUTE Function to Substitute Multiple Characters

To do multiple substitutions within a single formula, you can nest multiple SUBSTITUTE Functions.

Let’s say you have a text value like “art., amend., cl.” in cell B5, where “art.” stands for “article”, “amend.” stands for “amendment”, and “cl. ” means “clause”.

Nest the SUBSTITUTE Function to Substitute Multiple Characters

What you want is to substitute the three codes with full names. You may accomplish this by using three separate SUBSTITUTE formulae.

=SUBSTITUTE(B5,”art.”,”article”)

=SUBSTITUTE(B5,”amend.”,”amendments”)

=SUBSTITUTE(B5, “cl.”,”clause”)

Then nest them one inside the other.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,”art.”,”article”),”amend.”,”amendments”),”cl.”,”clause”)

To have it done, follow the steps below.

Step 1:

  • In cell C5, type the following formula.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"art.","article"),"amend.","amendments"),"cl.","clause")

Nest the SUBSTITUTE Function to Substitute Multiple Characters

Step 2:

  • Then, press Enter to see the change.

Nest the SUBSTITUTE Function to Substitute Multiple Characters

Step 3:

  • Copy the formula in the other required cells.

Therefore, you will see the substituted values shown in the screenshot below.

Nest the SUBSTITUTE Function to Substitute Multiple Characters


3. Perform the SUBSTITUTE Function with INDEX Function to Substitute Multiple Characters

In addition to the previous methods, you can also use the SUBSTITUTE Function with the INDEX Function to substitute multiple characters.

For example, you want to substitute red and blue with green and white consecutively. Multiple SUBSTITUTE functions can be nested, and the INDEX function can be used to feed in find/replace pairs from another table.

Perform the SUBSTITUTE Function with INDEX Function to Substitute Multiple Characters

To substitute multiple characters by applying both the SUBSTITUTE and the INDEX functions follow the steps below.

Step 1:

  • Firstly, enter the following formula in cell C5,
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(E5:E6,=SUBSTITUTE(SUBSTITUTE(B5,INDEX(E5:E6,1),INDEX(F5:F6,1)),INDEX(E5:E6,2),INDEX(F5:F6,2))

Where,

INDEX find range is E5:E6

INDEX find range is E5:E6

Perform the SUBSTITUTE Function with INDEX Function to Substitute Multiple Characters

Step 2:

  • Then, hit Enter to see the results.

Perform the SUBSTITUTE Function with INDEX Function to Substitute Multiple Characters

  • Finally, Copy the formula for other cells.

Perform the SUBSTITUTE Function with INDEX Function to Substitute Multiple Characters


4. Apply the REPLACE Function to Substitute Multiple Characters

In the following section, we will describe how to apply the REPLACE Function to substitute multiple characters in Excel. The REPLACE function in Excel allows you to swap one or several characters in a text string with another character or a set of characters.

The syntax of the Excel REPLACE function is as follows:

REPLACE(old_text, start_num, num_chars, new_text)

As you see, the REPLACE function has 4 arguments, all of which are compulsory.

Old_text – the original text (or a reference to a cell with the original text) in which you want to substitute some characters.

Start_num – the position of the first character within old_text.

Num_chars – the number of characters you want to replace.

New_text – the replacement text.

For example, to substitute the word “Face” for “Fact”, you can follow the steps below.

Apply the REPLACE Function to Substitute Multiple CharactersApply the REPLACE Function to Substitute Multiple Characters

Step 1:

  • Firstly, in cell D5, enter the following formula,
=REPLACE(B5, 4, 1,"t")

Apply the REPLACE Function to Substitute Multiple Characters

Step 2:

  • Then, press Enter to see the change.

Apply the REPLACE Function to Substitute Multiple Characters

Step 3:

  • To make all the changes shown in the screenshot below, copy the formulas for the required cells.

Apply the REPLACE Function to Substitute Multiple Characters


5. Nest the REPLACE Function to Substitute Multiple Character

Quite often, it’s probable that you’ll need to change multiple items in the same cell. Of course, you could perform one replacement, output an intermediate result into a new column, and then use the REPLACE function once more. However, using nested REPLACE functions, which allow you to do several replacements with a single formula, is a better and more professional option. Similar to the SUBSTITUTE function, you can also apply the nest in the REPLACE function.

Let’s say you have a list of phone numbers in column A that are formatted like “123-456-789” and you want to add space to make them look another way. To put it another way, you want to change “123-456-789” to “123 456 789”.

Nest REPLACE Function

To Substitute the multiple characters in multiple places, follow the steps below.

Step 1:

  • Type the following formula in cell C5 at first,
=REPLACE(REPLACE(B5,4,1," "),8,1," ")

Nest REPLACE Function

Step 2:

  • Secondly, press Enter to see the change in cell D5.

Nest REPLACE Function

Step 3:

  • Finally, copy the formula and repeat the steps for the required cells.

Nest REPLACE Function


6. Combining Multiple Functions to Substitute Multiple Character

In this method, you can apply REDUCE, LAMBDA, SUBSTITUTE, and OFFSET functions to substitute multiple characters. Here REDUCE function looks up the value in the cell range C5:C7, and if the criteria match, it substitutes the value in column and adjusts column C as well. Finally, this function will substitute the value modifying the original value.

Step 1:

  • Initially, select cell D5 and enter the formula below.
=REDUCE(B5,$C$5:$C$7,LAMBDA(a,b,SUBSTITUTE(a,b,OFFSET(b,0,1))))

Combining multiple functions

Step 2:

  • Then drag down the fill handle to copy the formula to cell D7.

Final output


7. Run A VBA Code to Substitute Multiple Character

Interestingly, you can apply VBA code to obtain the same to substitute multiple characters. In addition, you can change it as you want without concerning the character number or place, as seen in the two previously described functions.

Run A VBA Code

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

Step 1:

  • Firstly, press Alt + F11 to open the Macro-Enabled Worksheet.
  • Go to the Insert tab.
  • Then, select Module.

Run A VBA Code

Step 2:

  • Copy the following VBA code,
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
  • Where,

ThisWorkbook.Worksheets(“your current worksheet name”)

Range(“your reference cell”)

myStringToReplace = “value you want to substitute”

myReplacementString = “your substituted value”

  • Then, paste it into the program window
  • Press Enter to see the substituted number format.

Run A VBA Code


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

To conclude, I hope this article has provided detailed guidance to substitute multiple characters in Excel. All these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

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


<< Go Back to Find and Replace | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

2 Comments
  1. No need to use VBA anymore for this! Super simple formula now!

    =REDUCE(A1,$b$2:$b$6,LAMBDA(a,b,SUBSTITUTE(a,b,OFFSET(b,0,1))))

    Where A1 is the target cell to replace text
    b2:b6 is where the keywords are stored (texts to be replaced)
    c2:c6 (referenced by the offset) is where the replacing texts are stored

    Credit: Chandoo

    • Hello JACOB FLOYD,
      Thanks for your valuable contribution. Now this method is added to this article. Please let us know if you have any queries regarding this article.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo