excel – How to extract numbers from a text string in VBA

excel – How to extract numbers from a text string in VBA

You can use Regular Expressions.

Sub ExtractNumbers()
    Dim str As String, regex As regExp, matches As MatchCollection, match As match

    str = ID CSys ID Set ID Set Value Set Title 7026..Plate Top MajorPrn Stress 7027..Plate Top MinorPrn Stress 7033..Plate Top VonMises Stress

    Set regex = New regExp
    regex.Pattern = d+      ~~~> Look for variable length numbers only
    regex.Global = True

    If (regex.Test(str) = True) Then
        Set matches = regex.Execute(str)   ~~~> Execute search

        For Each match In matches
            Debug.Print match.Value ~~~> Prints: 7026, 7027, 7033
        Next
    End If
End Sub

Make sure you reference the VBA regex library:

  1. Open VBA editor
  2. Tools > References…
  3. Check Microsoft VBScript Regular Expression 5.5

To exact numbers in the form you want, try something like:

Sub dural()
    Dim s As String, i As Long, L As Long, c As String, temp As String
    s = [A1]
    L = Len(s)
    temp = 
    For i = 1 To L
        c = Mid(s, i, 1)
        If c Like [0-9] Then
            temp = temp & c
        Else
            temp = temp &  
        End If
    Next i

    temp =  & Application.WorksheetFunction.Trim(temp)
    temp = Replace(temp,  , ,)

    [B1] = temp
End Sub

enter

excel – How to extract numbers from a text string in VBA

You can use this function that splits the words and test for numeric:

Function numfromstring(str As String) As String
Dim strarr() As String
str = Replace(str, .,  )
strarr = Split(str)
Dim i As Long
For i = 0 To UBound(strarr)
    If IsNumeric(strarr(i)) Then
        numfromstring = numfromstring & , & strarr(i)
    End If
Next i

numfromstring = Mid(numfromstring, 2)
End Function

You would call it from the worksheet with a formula:

=numfromstring(A1)

Or from vba like this:

Sub try()
Dim str As String
str = ID CSys ID Set ID Set Value Set Title 7026..Plate Top MajorPrn Stress 7027..Plate Top MinorPrn Stress 7033..Plate Top VonMises Stress

Dim out As String
out = numfromstring(str)

Debug.Print out
End Sub

If you have Office 365 Excel you can use this array formula:

=TEXTJOIN(,,TRUE,IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,., ), ,REPT( ,99)),(ROW($1:$100)-1)*99+1,99))),TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,., ), ,REPT( ,99)),(ROW($1:$100)-1)*99+1,99)),))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode:

enter

Leave a Reply

Your email address will not be published. Required fields are marked *