find and replace \n wildcards like word

[julesgf]

Hi all
I'm trying to use excel find and replace to replace this:
=CONCATENATE('Master List'!B1)
=CONCATENATE(INDIRECT("'Master List'!B1"))
in many cells where the B1 ref is not the same
so i need it to find
'Master List'!??
and replace with
INDIRECT("'Master List'!\1\2")
where \1 is the col ref and \2 is the row ref
easy in word but i'm totaly stuck in excel

 

[Pete_UK]

Here's one way (bit convoluted):

Highlight the cells you want to change, then:

CTRL-H (Find/Replace):
Find what: =
Replace with: xyz=
Click Replace All

(This changes the formulae to text strings so that you don't get
errors when you do a partial replace). Keeping the same highlighted
cells:

CTRL-H
Find what: (
Replace with: (INDIRECT("
Click Replace All

CTRL-H
Find what: )
Replace with: "))
Click Replace All

CTRL-H
Find what: xyz=
Replace with: =
Click Replace All

This last one changes those text strings back to formulae. You might
prefer to use something different to xyz if that string may appear in
your formulae - I know that Dave Peterson often recommends using $$$$.

I'm not sure what your formula does, though - what are you
concatenating to what?

Hope this helps.

Pete

 

[CurlyDave]

Hi all
I'm trying to use excel find and replace to replace this:
=CONCATENATE('Master List'!B1)
=CONCATENATE(INDIRECT("'Master List'!B1"))
in many cells where the B1 ref is not the same
so i need it to find
'Master List'!??
and replace with
INDIRECT("'Master List'!\1\2")
where \1 is the col ref and \2 is the row ref
easy in word but i'm totaly stuck in excel

Maybe this site can help you
http://www.contextures.com/xlFunctions05.html

 

[julesgf]

Thanks Pete
thats almost exactly the way i achieved it in the end.
still not happy that excel doesnt have the functionality to do it directly
good to know that the "manipulate as text" solution is the way the pro's
(you guys) are doing it.

the point of all this is to make a sheet that i can lock down completely
which takes data from another sheet and lays it out for printing.
the indirects are to lock the cell ref (ie. A1 on print sheet allways = A1
from the data sheet no matter what the user does with the data sheet)

I'm concatinating one value in the example above because i noticed the
result of a concat with result of 0 is a blank call (desired for printing)

the other cells look more like:
=IF(INDIRECT("'Master List'!D18")=0,CONCATENATE(INDIRECT("'Master
List'!C18")),CONCATENATE(INDIRECT("'Master List'!C18")," ",CHAR(149),"
",INDIRECT("'Master List'!D18")))

Thanks again

 

[julesgf]

Thanks Pete
thats almost exactly the way i achieved it in the end.
still not happy that excel doesnt have the functionality to do it directly
good to know that the "manipulate as text" solution is the way the pro's
(you guys) are doing it.

the point of all this is to make a sheet that i can lock down completely
which takes data from another sheet and lays it out for printing.
the indirects are to lock the cell ref (ie. A1 on print sheet allways = A1
from the data sheet no matter what the user does with the data sheet)

I'm concatinating one value in the example above because i noticed the
result of a concat with result of 0 is a blank call (desired for printing)

the other cells look more like:
=IF(INDIRECT("'Master List'!D18")=0,CONCATENATE(INDIRECT("'Master
List'!C18")),CONCATENATE(INDIRECT("'Master List'!C18")," ",CHAR(149),"
",INDIRECT("'Master List'!D18")))

Thanks again