Substitute

T

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?
 
T

Trish

Is it possible to do 3 criteria?

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

Glenn

Trish said:
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)
 
G

Glenn

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

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)
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top