Formula Replacements with Wildcards

[Robert305]

Hello,
I have a question. I want to replace things with a regular CTRL+H dialog. Lets say my formulas are in this format:

=RIGHT(LEFT($B2, 4), 3)

Now, this formula has different targets spread around sheet. I want to remove everything around the $B2 and keep it since it differs for every formula. If I put =RIGHT(LEFT(*, 4), 3) in the find screen, it will find every entry. Is there a way I can specify the $B2 in the replacing screen so I can pass that on as a variable or something to the "replace with" field?

What I would like to achieve is similar to =SOMETHING*SOMETHINGELSE in the "replace with" field so that it takes the whatever was not selected in the find field ($B2 due to the asterisk) and gives me the result =SOMETHING$B2SOMETHINGELSE

Its a bit hard to explain so hopefully this is understandable. Basically I would like to specify something from the find field as a variable and pass it down to the "replace with" field so I can wrap things around it

Thanks for the help!

 

[onlyadrafter]

Hello,

The only way I can see to do it is by two step REPLACE

REPLACE =RIGHT(LEFT( with something and

REPLACE 4),3) with something else

unless you can somehow incorporate INDIRECT in your formula

e.g. replace your formula with

=RIGHT(LEFT(INDIRECT("$B" &ROW()), 4), 3)

then use CTRL + H

REPLACE

*$B" &ROW()*

REPLACE WITH

=LEFT(RIGHT(INDIRECT("B" &ROW()),4),3)


or whatever your formula needs to be.

Is this of any use?

 

[Robert305]

Hello,

The only way I can see to do it is by two step REPLACE

REPLACE =RIGHT(LEFT( with something and

REPLACE 4),3) with something else

unless you can somehow incorporate INDIRECT in your formula

e.g. replace your formula with

=RIGHT(LEFT(INDIRECT("$B" &ROW()), 4), 3)

then use CTRL + H

REPLACE

*$B" &ROW()*

REPLACE WITH

=LEFT(RIGHT(INDIRECT("B" &ROW()),4),3)


or whatever your formula needs to be.

Is this of any use?

Hi,
the problem was that I was replacing a formula and that single replaces were breaking my formula. In case anyone is wondering, I ended up disabling the formula (ie Replace =RIGHT(LEFT( with RIGHT(LEFT( to get rid of the = sign) and then did multiple replaces around my anchor. Once I finished I replaced RIGHT(LEFT( back to =RIGHT(LEFT( in order to convert them back to formulas