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.
- Forum topic that is similar to the above topic.