Keep text matching a wildcard spec in replace/substitute
 

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?

Keep text matching a wildcard spec in replace/substitute
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hershmab" wrote:

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?

Keep text matching a wildcard spec in replace/substitute
 

In article ,
Hershmab wrote:

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?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Keep text matching a wildcard spec in replace/substitute
 

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.

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hershmab" wrote:

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?