Extract Numbers From a String Using VBA

When we develop VBA applications, sometimes we need to develop functions or subroutines to extract numbers from strings. So in this lesson you will learn a few different ways to extract numbers from a string using VBA.

Assume we have a string like this.

234sTsur45$p^

We are going to learn how to extract only 23445 from the above text string. There are few different ways to do this.

Method 1 - Using IsNumeric function

In this method we are going to use an inbuilt function called “IsNumeric” to extract only numbers from the string. Let’s name this subroutine as ExtractNumbers.

Sub ExtractNumbers()

End Sub

First we need to declare a few variables.

Dim MyString As String
Dim Tmp_Char As String
Dim ResultString As String
Dim i As Integer

Then we can assign our string to the MyString variable.

MyString = "234sTsur45$p^"

Next we need a For Next statement to iterate through each character of the string.

For i = 1 To Len(MyString)

Next i

Inside this For Next statement we can use the Mid function to extract each character one by one. Each character will be assigned to the Tmp_Char variable temporarily.

For i = 1 To Len(MyString)
     Tmp_Char = Mid(MyString, i, 1)
Next i

Now use IsNumeric function inside an If statement to check whether each character is a value or not. If the IsNumeric function returns true then we add that number to the ResultString variable.

For i = 1 To Len(MyString)
     Tmp_Char = Mid(MyString, i, 1)
     If IsNumeric(Tmp_Char) = True Then
         ResultString = ResultString & Tmp_Char
     End If
Next i

Want to learn more about the IsNumeric function? Check this post.

IsNumeric Function

This is the complete code of the first method.

Sub ExtractNumbers()

     Dim MyString As String
     Dim Tmp_Char As String
     Dim ResultString As String
     Dim i As Integer

     MyString = "234sTsur45$p^"

     For i = 1 To Len(MyString)
         Tmp_Char = Mid(MyString, i, 1)
         If IsNumeric(Tmp_Char) = True Then
             ResultString = ResultString & Tmp_Char
         End If
     Next i

     Debug.Print ResultString

End Sub

23445 will be printed in the immediate window when you run the above subroutine.

Method 2 - Using Select Case statement

In this method we are going to use Select Case statement instead of the If statement and IsNumeric function to extract numbers from the string. Below is the complete code of the second method.

Sub ExtractNumbers_Method2()

     Dim MyString As String
     Dim Tmp_Char As String
     Dim ResultString As String
     Dim i As Integer

     MyString = "234sTsur45$p^"

     For i = 1 To Len(MyString)
         Tmp_Char = Mid(MyString, i, 1)
         Select Case Tmp_Char
         Case 0 To 9
             ResultString = ResultString & Tmp_Char
         End Select
     Next i

     Debug.Print ResultString

End Sub

In this method Select Case statement is used to check whether the character is equal to value from 0 to 9. If the character is equal to value from 0 to 9 then those characters are appended to the ResultString.