How to find numbers from a string?


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

This question is tagged with string vba excel

~ Asked on 2011-08-30 06:10:49

The Best Answer is


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

    ' 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:


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

~ Answered on 2011-08-30 06:20:47


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

~ Answered on 2011-09-14 08:33:27

Most Viewed Questions: