exel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2778037%22%20slang%3D%22en-US%22%3Eexel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778037%22%20slang%3D%22en-US%22%3E%3CP%3Eam%20trying%20to%20combine%20left%20formula%20with%20a%20check.%20IE%20column%20B%20contains%20name%20ABC123%20or%20XYZ123.%20I%20want%20to%20select%20the%20left%203%20characters%2C%20but%20also%20want%20to%20check%20to%20say%20if%20the%20result%20is%20ABC%20then%20make%20the%20answer%20%22NEW%22%2C%20not%20%22ABC%22.%20I%20tried%20using%20%3Dif(LEFT(B1%2C3)%3D%22ABC%22%2CNEW%22%2C%3DLEFT(B1%2C3))%20-%20this%20didnt%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2778037%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778126%22%20slang%3D%22en-US%22%3ERe%3A%20exel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164451%22%20target%3D%22_blank%22%3E%40gavin930%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20should%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(LEFT(B1%2C3)%3D%22ABC%22%2CNEW%22%2CLEFT(B1%2C3))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESlightly%20simpler%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUBSTITUTE(LEFT(B1%2C3)%2C%22ABC%22%2C%22NEW%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@gavin930 

That should be

 

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

 

Slightly simpler:

 

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

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