Formula help please

[blackie42]

Using this formula in cell C2

=REPLACE(B2,FIND("B",B2),LEN("B"),"C")

gives the result of C (in C2) if theres a B (in B2)

I also want to get a result of D in C2 if theres an I in B2 so

=REPLACE(B2,FIND("I",B2),LEN("I"),"D")

Both work separately but can't figure how to nest the 2 functions together

help please - or is there another easier way to get the same result?

many thanks
Jon

[SamT]

Jon, I don't trust your explanation. Do me a favor and copy the working formula from the Formula Bar and paste it directly into the post.

See clear contents of adjacent cell(s) on (http://www.vbaexpress.com/forum/showthread.php?51309-clear-contents-of-adjacent-cell%28s%29-on-change)for reason.

[Aussiebear]

Just guessing here but try;

=Replace(B2,Find(Or("B",B2),Len("B"),"C"),("I",B2)Len("I"),"D")

[blackie42]

Hi Guys,

Copied from formula bar

=REPLACE(B2,FIND("B",B2),LEN("B"),"C") & =REPLACE(B3,FIND("I",B3),LEN("I"),"D")

Can’t get your formula to work Aussie but thanks for trying

The contents of B2 would always be a single letter either B or I

thanks
Jon

[blackie42]

Solution...

=SUBSTITUTE(SUBSTITUTE(B2,"B","C"),"I","D")

thanks for your help guys
regards
Jon

[SamT]

=IF(B2="B","C","D")

[blackie42]

Hi Sam,

It really was simple in the end - I use these types of IF statements regularly and it never clicked. Getting Old!

Thanks again