Using SUBSTITUTE with wildcard characters

To illustrate my issue, use the following simple example.

I want to be able to replace any text that comes between “replace” and “that” to “it is done”. For example, the two following strings:

blah blah blah replace this and that blah
blah blah blah replace this and some other text and that blah

to this string

blah blah blah it is done blah

using the SUBSTITUTE function in excel using wildcard characters.

This is the formula that I’m using:

=SUBSTITUTE("blah blah blah replace this and that blah","replace*that","it is done")

The formula isn’t replacing the text, though, and evaluates to “blah blah blah replace this and that blah”

What is going on?

Leave a Comment