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

 

[Eric W]

Here's sort of a brute force approach:

=AND(UPPER(MID(A1,ROW($1:$3),1))>="A",UPPER(MID(A1,ROW($1:$3),1))<="Z")
Confirmed with Control+Shift+Enter.

It should work if you don't insert any rows before 4.

 

[pgc01]

Hi Eric

If you use a constant array, the formula is not a formula array. Also, the string comparison is case insensitive

"b">"a"
"b">"A"
"B">"a"
"B">"A"

Using your formula:

=AND(MID(A1,{1,2,3},1)>="A",MID(A1,{1,2,3},1)<="Z")

 

[Eric W]

Nicely improved.

 

[Rick Rothstein]

Hi Rick

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

I think you have to test the characters separately.

Of course!

Hi Eric

Using your formula:

=AND(MID(A1,{1,2,3},1)>="A",MID(A1,{1,2,3},1)<="Z")

Nicely improved.

Agreed!

 

[HockeyDiablo]

Which ones does it not omit?

- / ¥ ?

-can install tomorrow if in stock Sp/Cable NOQ <tbody> </tbody>

<tbody>
</tbody>

SE¥9-12¥ Stuck Open C?ALLED.VERY HAPPY WITH GERALD <tbody> </tbody>

<tbody>
</tbody>

 

[HockeyDiablo]

Thank you gentlemen, this is working great now. Appreciate all of the help. Saved my neck for sure.