Question - Substitution function is substitution its own values

Hello,

I was trying to replace certain values with other values (e.g., 1 to 100, 2 to 80) and used the following syntax:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,1,100), 2,80), 3,60), 4,40), 5,20), 6,0)

However, for the value 3, it generated 00 as output instead of 60. It had changed 3 to 60, and then changed the 6 in 60 to a 0, which resulted in 00. Is there a way to prevent Excel from double substituting like this?

Thank you.

Here's an alternative:

=CHOOSE(B3,100,80,60,40,20,0)

and another way:

=100-(B3-1)*20

both of which return numeric values.

Hope this helps.

Pete

If B3 only contains 1 of the numbers to be substituted then I would suggest using either of Pete's methods.

If the digits / characters to be substituted are part of a longer string in B3, then you will need substitute to be able to change only part of it.

The rule to follow with multiple substitutions like this is to order them so that you substitute x before you substitute with anything that contains x.

So in your formula, you need to substitute 6 for 0 before substituting 3 for 60
Likewise, 2 needs to be substituted before 5 is substituted by 20 to prevent 20 from becoming 800.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,6,0), 2,80), 3,60), 4,40), 5,20), 1,100)

I didn't point out explicitly, though I hinted at it in my final comment, that the SUBSTITUTE function returns text values, which is why you got 00 rather than just 0. If you want numbers rather than text values then you can multiply the whole of the nested SUBSTITUTE formula by 1, or add zero (i.e. add *1 or +0 at the end).

If you DO want text values, then with my formulae you can add &"" to the end of the formulae.

Hope this helps.

Pete