If Statement based on Alpha or Numeric

R

Ryan

I am trying to do an if statement based on whether a character is numeric or
alpha.

e.g. I have the following spreadsheets:

A
1 01-BHZ
2 01-049

Basically what I am wanting to do is an if statement based on a mid formula.

i.e. if mid(A1,4,1) is numeric then "Numeric", if not "Alpha"

Is there a formula I can use? Is it a public function with a bit of VBA?

Please help

Regards,

Ryan
 
A

Alex Delamain

Maybe not the slickest formula but ......

01-BHZ = 0
01-049 = 1
01-AA2 = 0
01-65B = 0

=IF(ISERROR(VALUE(MID(A1,4,3))),0,1
 
M

mzehr

Hi Jason,
You have my curiosity piqued. What is the purpose of the
additional part you added of "1*Mid" as opposed to
just "Mid"?

Thanks
Mike
 
A

Alex Delamain

I am guessing it has the same effect as the value() in my formula
Without it the 4th digit remains a text string so even if it is a
your formula returns "Alpha
 
J

Jason Morin

A1 contains a text string. When you extract the 4th
character, even if it is a number, that 4th character
still is a text string. In fact, select the MID(A1,4,1)
part of the formula in the formula bar and press F9.
You'll see that in your second example it returns "0",
not 0.

So to convert to a number, you can multiply that text
string by a 1. "0" becomes 0, "3" becomes 3, etc. When
you check it with ISNUMBER, you get TRUE. But if you
multiply a character like "B" by 1, you get #VALUE! and
ISNUMBER returns FALSE.

With Excel, a number is not always a number.

HTH
Jason
Atlanta, GA
 
M

mzehr

Jason,
Well thought out. Thanks.
Mike
-----Original Message-----
A1 contains a text string. When you extract the 4th
character, even if it is a number, that 4th character
still is a text string. In fact, select the MID(A1,4,1)
part of the formula in the formula bar and press F9.
You'll see that in your second example it returns "0",
not 0.

So to convert to a number, you can multiply that text
string by a 1. "0" becomes 0, "3" becomes 3, etc. When
you check it with ISNUMBER, you get TRUE. But if you
multiply a character like "B" by 1, you get #VALUE! and
ISNUMBER returns FALSE.

With Excel, a number is not always a number.

HTH
Jason
Atlanta, GA

.
Click to expand...
 
Top