Excel: How to Use SUBSTITUTE Function with Wildcards

You can use the SUBSTITUTE function in Excel to substitute a specific substring in a cell with another substring.

Unfortunately, the SUBSTITUTE function doesn’t work with wildcard characters.

However, you can use the following formula to replace text in a cell using an approach that is similar to using wildcard characters:

=IFERROR(LEFT(A2,FIND("Mavs",A2)+LEN("Mavs"))&"are an awesome "&RIGHT(A2,LEN(A2)-FIND("team",A2)+LEN("team")-3), A2)

This particular formula uses a combination of the LEFT, RIGHT, FIND and LEN functions to replace all text in cell A1 between the strings “Mavs” and “team” with the text “are an awesome” instead.

The following example shows how to use this formula in practice.

Example: Substitute Text Using Wildcard Characters in Excel

Suppose we have the following list of phrases in Excel:

We can use the following formula to substitute all text in cell A2 between the strings “Mavs” and “team” with the text “are an awesome” instead.

=IFERROR(LEFT(A2,FIND("Mavs",A2)+LEN("Mavs"))&"are an awesome "&RIGHT(A2,LEN(A2)-FIND("team",A2)+LEN("team")-3), A2)

We’ll type this formula into cell B2 and then click and drag it down to each remaining cell in column B:

Excel SUBSTITUTE with wildcard characters

Notice that all text between the strings “Mavs” and “team” have been replaced with the text “are an awesome” instead.

Note that we used the IFERROR function to specify that if “Mavs” and “team” couldn’t be found then we should simply return the original phrase from column A.

Without the IFERROR function, the formula would return a #VALUE! error if the strings we searched for could not be found.