How to find numbers from a string?

Question

I need to find numbers from a string. How does one find numbers from a string in VBA Excel?

Answer 1

Assuming you mean you want the non-numbers stripped out, you should be able to use something like:

Function onlyDigits(s As String) As String     ' Variables needed (remember to use "option explicit").   '     Dim retval As String    ' This is the return string.      '     Dim i As Integer        ' Counter for character position. '      ' Initialise return string to empty                       '     retval = ""      ' For every character in input string, copy digits to     '     '   return string.                                        '     For i = 1 To Len(s)         If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then             retval = retval + Mid(s, i, 1)         End If     Next      ' Then return the return string.                          '     onlyDigits = retval End Function 

Calling this with:

Dim myStr as String myStr = onlyDigits ("3d1fgd4g1dg5d9gdg") MsgBox (myStr) 

will give you a dialog box containing:

314159 

and those first two lines show how you can store it into an arbitrary string variable, to do with as you wish.

Answer 2

Regular expressions are built to parse. While the syntax can take a while to pick up on this approach is very efficient, and is very flexible for handling more complex string extractions/replacements

Sub Tester()      MsgBox CleanString("3d1fgd4g1dg5d9gdg") End Sub  Function CleanString(strIn As String) As String     Dim objRegex     Set objRegex = CreateObject("vbscript.regexp")     With objRegex      .Global = True      .Pattern = "[^\d]+"     CleanString = .Replace(strIn, vbNullString)     End With End Function