If the first 3 characters are alpha, then return true

[HockeyDiablo]

Looking for a formula to check the first 3 characters of a string and return TRUE if all three are "A-Z only"

=IF(ISNUMBER(VALUE(LEFT(J6,3)))=FALSE,TRUE,FALSE)

abc123 = true
123abc = false
a1b2c3 = false

 

[Special-K99]

If(istext(left(j6,3)),true,false)

 

[HockeyDiablo]

istext, good Lord I am glad its friday....

however, I am getting TRUE for this: "a 12345"

 

[JoeMo]

Try:

=IF(AND(ISERROR(VALUE(MID(A1,1,1))),ISERROR(VALUE(MID(A1,2,1))),ISERROR(VALUE(MID(A1,3,1)))),TRUE,FALSE)

 

[HockeyDiablo]

then how about a formula spread into 3 columns?:

=if
Col"A" first 3 are Letters
Col"B" first 3 are Numbers
Col"C" first 3 are Letters
------------------------------
=True


Anyway to not pick up symbols? Only if they are A-Z should the Letters be True

 

[Rick Rothstein]

Looking for a formula to check the first 3 characters of a string and return TRUE if all three are "A-Z only"

=IF(ISNUMBER(VALUE(LEFT(J6,3)))=FALSE,TRUE,FALSE)

abc123 = true
123abc = false
a1b2c3 = false

I think this may work...

=AND(A1>="AAA",LEFT(A1,3)<="ZZZ",FIND(" ",A1&" ")>3)

 

[HockeyDiablo]

I think this may work...

=AND(A1>="AAA",LEFT(A1,3)<="ZZZ",FIND(" ",A1&" ")>3)

It sure did, but then again you created it so of course it would work. Thanks again.

**this formula also omits symbols, perfect!!!

 

[HockeyDiablo]

ok, it only omits some symbols.

 

[Rick Rothstein]

ok, it only omits some symbols.

Which ones does it not omit?

 

[pgc01]

Hi Rick

Since strings compare left to right, "B#$" > "AAA"

I think you have to test the characters separately.