Substitute

[Trish]

Help! I am using a workbook where in sheet 3 I'm looking at sheet 2 to see
if a value exists "x" in a column -- if it exists, I want to replace that
value "x" with a value found in sheet 1 "4" -- I was able to create that
formula as follows:

=SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5)

However, I also want to look for value "i" in sheet 2 and if it exists,
replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get
that formula to work, I tried:

=(SUBSTITUTE(Master!G3,"x",'Curriculum Duration
Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Curriculum Duration
Estimates'!$G$7))

Any ideas?

 

[Trish]

Thanks Glenn, that worked!

=SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration
Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7)

 

[Trish]

Is it possible to do 3 criteria?

=SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration
Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7)

 

[Trish]

is it possible to then sum the substitution results?

 

[Glenn]

Help! I am using a workbook where in sheet 3 I'm looking at sheet 2 to see
if a value exists "x" in a column -- if it exists, I want to replace that
value "x" with a value found in sheet 1 "4" -- I was able to create that
formula as follows:

=SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5)

However, I also want to look for value "i" in sheet 2 and if it exists,
replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get
that formula to work, I tried:

=(SUBSTITUTE(Master!G3,"x",'Curriculum Duration
Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Curriculum Duration
Estimates'!$G$7))

Any ideas?

=SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration
Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7)

 

[Glenn]

Yes, you can nest additional SUBSTITUTE() functions. Just replace "Master!G3"
in the existing formula with another complete SUBSTITUTE() function.

 

[Glenn]

To change the result from text into a number, precede the calculation with "--":

=--SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration
Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7)

 

[Trish]

Thanks for this answer and the other... still having trouble since I've got
some cells where there is no change because it has nothing in it so if I use
"--" I get a value problem but I think I can fix that.