SUBSTITUTE function - 3 digit Problem, only checks first 2 digits.

[phililpB]

Hello,

I'm using substitute nested many times to turn formulas with cell references into equations with text. It's a multipart process starting with a formula-to-text function generating a secondary array, some other stuff, and then a huge string of nested substitute functions generating a third array with the equations.

However, substitute seems to only reference the first two digits regardless of what i do.

For instance running =SUBSTITUTE(SUBSTITUTE(E8,"c14"," yes"),"c144", "no") on two cells, one with c14 and one with c144 properly returns yes for the first, but for the second returns yes4. So it seems to be only evaluating the first two digits, falsely recognizing the second cell as "c14" replacing that and leaving the additional 4.

Is there a solution to this problem? Any help would be greatly appreciated.

 

[Max1616]

I don't really know what your goal is here, but have you tried flipping the arguments?
=SUBSTITUTE(SUBSTITUTE(E8,"c144"," no"),"c144", "yes")

 

[phililpB]

Hi Max,

One thing. It's c14 vs c144. You have c144 two times, but maybe that's a typo.

Good point though! Maybe having it evaluate 3digit numbers first will sort it out.

However, if already painstakingly constructed my monster nested statement. If there was a way to salvage it and get it to evaluate correctly that would be icing on the cake.

 

[Max1616]

Yea, that was a typo. I'm not sure of a better way to do this without looking at your full nested formula

 

[phililpB]

It worked!!!

Thank you...

Guess its back to the dungeon to create a new monster!

 

[Jonmo1]

You have to consider the order the functions are performed.

The Innermost functions go first...

So it goes like this
if E8 - c144
=SUBSTITUTE(SUBSTITUTE(E8,"c14"," yes"),"c144", "no")
=SUBSTITUTE(SUBSTITUTE("c144","c14"," yes"),"c144", "no")

c14 does exist in the string "c144", so substitute returns " yes4"
=SUBSTITUTE(" yes4","c144", "no")

c144 doesn't exist within the string " yes4", so substitute then returns " yes4"