VBA Get Numbers with in a String

Get all Digits or Numbers within a String

If you want to extract all the digits present in alphanueric string like “alpha123how23its98done”, use this function.

A similar VBA function to this is: IsNumeric. This function return only a boolean value: True if string ony has numers, FALSE if it is alphabet or a mix of alphanumeric.

This function uses a regular expression within VBA to match all non numeric values, then replacing them with null string.

And this reults in only numeric values retrieved in the same order as present within the text.

Function getNumbersInString(sText As String) As String
    'Define Regex or include 'Microsoft VBScript Regular Expression from Tools->Reference
    Dim objRegex
    Set objRegex = CreateObject("vbscript.regexp")
    
    'Set Pattern & Execute Regex
    With objRegex
        .Global = True          'To perform function all match within string
        '.Pattern = "[^\d]+"    '[NOT digit] one or more occurance
        .Pattern = "[\D]+"      '[NOT digit] one or more occurance
        getNumbersInString = .Replace(strIn, vbNullString) 'Replace non digits with null - hence results in only numbers
    End With
End Function

Insert a new mobule in Excel VBE editor, then copy paste this code. and test by calling it as getNumbersInString(“alpha123how23its98done”).

This will return the digits: 1232398. Since we are using Regex function to match the pattern, it is easy to handle different scenarios in a simple regular expression patter. Otherwise, we will have to handle each condition in a big loop & with multiple if conditions.

External reference:

1. Forum topic that is similar to the above topic.