exel formula

[gavin930]

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]

@gavin930 

That should be

=IF(LEFT(B1,3)="ABC",NEW",LEFT(B1,3))

Slightly simpler:

=SUBSTITUTE(LEFT(B1,3),"ABC","NEW")

[#3]

@gavin930 

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.]