• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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

Open in new window



Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
3 Solutions
 
als315Commented:
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

Open in new window

0
 
Rgonzo1971Commented:
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

Open in new window

Regards
0
 
mvidasCommented:
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

Open in new window

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

Open in new window

Matt
0
 
FordraidersAuthor Commented:
Thanks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now