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

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

--
Regards,
Tom Ogilvy

"Ryan" wrote in message
...
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

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

"Ryan" wrote in message
...
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

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

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

--
Regards,
Tom Ogilvy

"Ryan" wrote in message
news:3170FAEB-42FB-4AD1-AE5B-
...
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


.