Check to see if a cell starts with an alphanumeric symbol and return value if it is

[MildewMan]

Hello,

I need some help with making one cell check another cell's first character to see if it is either A-Z (caps don't matter) or 0-9 and return the value of a single cell if it is. The code that I am current using is:

=IF(OR(AND(CODE(LEFT(D$10,1))>=65,CODE(LEFT(D$10,1)<=90)),AND(CODE(LEFT(D$10,1))>=97,CODE(LEFT(D$10,1)<=122)),AND(CODE(LEFT(D$10,1))>=48,CODE(LEFT(D$10,1)<=57))),C12,"")

As I'm sure most know, this is supposed to change the first digit of cell D10 to ASCII and then check to see if it falls within one of those ranges that corresponds to A-Z, a-z, and 0-9, return a blank if not and the value of C12 if true. The problem is, I am able to put in symbols such as ?,!, etc into D10, and it still returns the value of C12. When typing in just the CODE(LEFT) part of the formula in another cell to see what D10 returns, it gives me a value of 63 for a ?, which falls out of those ranges, so the formula should return a blank correct? Is there any suggestion on what I can do to fix this problem?

Thank you in advance.

[Pete_UK]

Here's another approach that is a bit easier to follow (and uses fewer function calls):

=IF(ISNUMBER(FIND(LEFT(D$10),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789")),C12,"")

Hope this helps.

Pete

[MildewMan]

Here's another approach that is a bit easier to follow (and uses fewer function calls):

=IF(ISNUMBER(FIND(LEFT(D$10),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789")),C12,"")

Hope this helps.

Pete

This worked perfectly. Thank you!

[Olly]

Another alternative:

=IF(LOOKUP(CODE(LEFT(D$10,1)),{0,48,58,65,91,97,123},{0,1,0,1,0,1,0}),C12,"")

[XOR LX]

If you want to go down the CODE route:

=IF(OR(ISNUMBER(0+LEFT(D$10)),ABS(77.5-CODE(UPPER(LEFT(D$10))))<13),C12,"")

Regards