If Statement based on Alpha or Numeric

[Guest]

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

 

[Tom Ogilvy]

=if(iserror(1*mid(A1,4,1)),"Text","Numeric")

 

[Bernie Deitrick]

Ryan,

With a worksheet function:
=IF(ISNUMBER(VALUE(MID(A1,4,1))),"It's a number","It's not a number")

In a macro:

Sub Macro1()
Dim myNum As Integer
On Error GoTo NotNumber
myNum = CInt(Mid(Range("A1").Value, 4, 1))
MsgBox "It's a number"
Exit Sub
NotNumber:
MsgBox "It's not a number"
End Sub

Or

Sub Macro2()
Dim myNum As Integer
myNum = Asc(Mid(Range("A1").Value, 4, 1))
If myNum >= Asc("0") And myNum <= Asc("9") Then
MsgBox "It's a number"
Else
MsgBox "It's not a number"
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Kris]

Interesting solution.

I was going to suggest something more like:

=IF(AND(MID(A4,4,1)>="0",MID(A4,4,1)
<="9"),"Numeric","Alpha")

Your solution is shorter and has more finesse to it,

Sincerely,

Kris