Keep text matching a wildcard spec in replace/substitute

[Hershmab]

I want to replace a text string (in many formulas) that contains fixed
initial and terminal strings to be replaced and an intermediate string that
needs to be left in place. For example I want to change:
VLOOKUP(<inter-string>, 1, FALSE)
into
MATCH(<inter-string>, 0)

How can I do this when the REPLACE command does not preserve the text
matching the wildcard * character?

 

[Luke M]

You may have to do it in a multi-step process.

First, find & replace "=" with "ZZZ="
to prevent formula errors from generating

Find & Replace "VLOOKUP" with "MATCH"
Find & Replace ",1,FALSE" with ",0"

Finally, restore formula functionality by
Find & Replace "ZZZ=" with "="

Not a perfect solution, but it works.

 

[Domenic]

I want to replace a text string (in many formulas) that contains fixed
initial and terminal strings to be replaced and an intermediate string that
needs to be left in place. For example I want to change:
VLOOKUP(<inter-string>, 1, FALSE)
into
MATCH(<inter-string>, 0)

How can I do this when the REPLACE command does not preserve the text
matching the wildcard * character?

Somewhat unclear... Can you provide a small sample of the data, along
with the expected result?

 

[Hershmab]

This is not an ideal solution simply because there may be other instances of
"=VLOOKUP" without ",1,FALSE". I need some way of picking out just the
formulae I need to change, so that I do not make changes elsewhere that I
have to reverse somehow.

I tried using autofilter for that purpose, but I have not found out how to
use autofilter on formulae, only on values.