Extract Number from AlphaNumeric string

excel 2010 vba:

What I need:
str2  is a string variable
falpha is just my holder variable

str2 usually is  something like:   B2  C3   D34   B21   AC3    DE45    etc...

What I need:
I need a variable just to hold the Numeric part of the str2

The code below with extract the alpha part i need
``````If IsNumeric(Mid(str2, 2, 1)) Then
falpha = Left(str2, 1)
Else
falpha = Left(str2, 2)
End If

str = falpha
``````

Thanks
fordraiders
LVL 3
Who is Participating?

Commented:
Hi,

pls try

``````Function FindNumber(MyString As String) As Double
strFormula = "=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(\$1:\$256),1)),0),COUNT(1*MID(A1,ROW(\$1:\$256),1)))"
strFormula = Replace(strFormula, "A1,", Chr(34) & MyString & Chr(34) & ",")
FindNumber = Evaluate(strFormula)
End Function
``````
Regards
0

Commented:
``````Dim i As Integer
Dim Str As String, c As String
Dim falpha As String, fnumeric As String
falpha = ""
fnumeric = ""
For i = 1 To Len(Str2)
c = Mid(Str, i, 1)
If IsNumeric(c) Then
fnumeric = fnumeric & c
Else
falpha = falpha & c
End If
Next i
Debug.Print falpha, fnumeric
``````
0

Commented:
Simply because I couldn't pass up this question without giving a Regular Expression answer:
``````Function ExtractNumber(str2 As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "\D"
ExtractNumber RegEx.Replace(str2, "")
End Function
``````
It will return whatever numbers are in the string, regardless of what is sent:
`````` Debug.Print ExtractNumber("B2")
'2

Debug.Print ExtractNumber("C3")
'3

Debug.Print ExtractNumber("DE45")
'45

Debug.Print ExtractNumber("B2  C3   D34   B21   AC3    DE45")
'233421345
``````
Matt
0

Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.