Forum Discussion
exel formula
am trying to combine left formula with a check. IE column B contains name ABC123 or XYZ123. I want to select the left 3 characters, but also want to check to say if the result is ABC then make the answer "NEW", not "ABC". I tried using =if(LEFT(B1,3)="ABC",NEW",=LEFT(B1,3)) - this didnt work
2 Replies
You are almost there.
= IF(LEFT(names,3)="ABC","NEW",names)Just a case of removing the surplus "=".
[I have also used a defined name, but that is just personal style whereby I never use direct cell references and avoid relative referencing where possible.]
That should be
=IF(LEFT(B1,3)="ABC",NEW",LEFT(B1,3))
Slightly simpler:
=SUBSTITUTE(LEFT(B1,3),"ABC","NEW")
