If statement with "Left"

[ammartino44]

Hello. How would I do an if statement to say, If the 8th character is a letter, return true, if it is a number return false?

123456 Harvard

My attempt: If(Left(Cell,8)=.....

Thanks

[Ace_XL]

Try

=NOT(ISNUMBER(MID(A1,8,1)*1))

EDIT: MID will always return TEXT hence ISTEXT will not work

[daddylonglegs]

=NOT(ISNUMBER(MID(A1,8,1)*1))

...or you could save a function call and use this:

=ISERROR(MID(A1,8,1)+0)

[ammartino44]

Great. Thanks. Both of these worked. But I tried to put it in conditional formatting and it didn't work. Why is this?

Edit: Also, what should I do if there is a blank cell. I would like these cells to return False.

Thanks

[{=OR(value=array)}]

great. Thanks. Both of these worked. But i tried to put it in conditional formatting and it didn't work. Why is this?

Edit: Also, what should i do if there is a blank cell. I would like these cells to return false.

Thanks

=if(a1="",false,iserror(mid(a1,8,1)+0))

[ammartino44]

Actually this formula worked =NOT(ISNUMBER(MID(A1,8,1)*1)), the other one didn't. Now I just need to figure out how to ignore empty cells because those cells return false (and then true because of the NOT).

Edit: I tried putting in $A40="" to format the blanks a particular way but it doesn't work right. After I press enter it returns this ="$A40="""""

[Tony Valko]

What if the 8th character is a space or a punctuation mark (if that's even possible)?

Here's something that's a bit more robust.

=AND(LEN(A2)>=8,ISERR(-MID(A2,8,1)))