use replace to edit a formula with wildcards

[Guest]

I have the following formula:
='Grant Project 1'!J314
but it's repeated in a column, each time with a different number
(i.e. ='Grant Project 1'!J314, ='Grant Project 1'!J317, ='Grant Project
1'!J320...)
and I want to replace each occurance with
='Grant Project 1'!J$314
Is there a way to use the replace tool to insert the '$' by using wildcards
in place of the numbers?

Thanks in advance.

 

[Guest]

If you want all the references to point to 'Grant Project 1'!J314, and all
the references are the same up to the '3' (i.e. you don't have anything like
'Grant Project 1'!J884) you can use this:

Search For: 'Grant Project 1'!J3*
Replace With: 'Grant Project 1'!J314

If that '3' does change, just change what you search for to 'Grant Project
1'!J*

Basically put the asteric after the spot in the formula you don't want to
change.

 

[Richard Neville]

If the repetition is always in column J, you can use Replace easily enough.
"Find what" would be !J, and Replace with !$J. If there are other columns
you will have to Replace separately for each. Try this on one or two numbers
before you "Replace all." You can't use a wild card in the Replace function.

 

[Guest]

Richard -
You can use the wildcard in the replace feature, you simply use the asterick
(*).

 

[Guest]

But I need to insert a $ after the J. I've tried
Search For: ='Grant Project 1'!J*
Replace With: ='Grant Project 1'!J$*
but I get an error message...?

 

[Guest]

Why are you replacing with J$*? I thought you needed J$314?

What is the error message you are receiving?

 

[Harlan Grove]

db wrote...

Richard -
You can use the wildcard in the replace feature, you simply use the asterick
(*).

....

* or ? (or ~) in the Replace field each represent literal characters.
If cell A1 contained the formula =X99 and the find text were X* and the
replacement text X$*, then Excel would display an error message when
you tried this replacement. Change A1 to the text X99 and run the
repacement again to see that it changes the cell to X$*.

In other words, * and ? and ~ aren't treated like wildcards in replace
text.

 

[Guest]

Oh I understand Richard's comment. Ya "wild card's" aren't treated as wild
cards in the "Replace" line, only in the "Search" line.

 

[Guest]

I need to maintain all the numbers after the J (J314, J317, J320...) and make
them J$314, J$317, J$320...
The message is "The formula you typed contains an error"

 

[Guest]

Got ya... this should work in that case:

Search For: J
Replace with: J$

 

[Harlan Grove]

db wrote...

Got ya... this should work in that case:

Search For: J
Replace with: J$

....

That'll also fubar the formulas unless you're matching case. Even then
it wouldn't be robust if there could be worksheet names that include
capital J's, e.g., 'Jan Budget'. The *safe* way to do this is to find

!J

and replace it with

!J$

which could still alter text within test constants.

 

[Guest]

Of course. Sometimes the answer is right there in front of your face...
Thanks much.