Extract Only Numbers From a String Using VBA

In this lesson you will learn how to extract only numbers from a string using VBA. Here are some examples where extracting only numbers from a string can be useful.

Data cleaning in Excel
You have an Excel sheet with a column containing alphanumeric data, and you want to extract only the numeric part for analysis or calculation purposes.

Financial Data Processing
You are working with financial data that includes transaction descriptions, and you want to extract only the transaction amounts.

Web Scraping
You are scraping data from a website, and some of the retrieved text includes both numeric and non-numeric characters. You want to filter out only the numeric values.

Above are a few scenarios where you may need to extract only numbers from a string using VBA.

Now let’s see how we can develop a VBA program to do this. Let’s create a function to extract numbers from a given string. Name of the function is ExtractNumbers. It takes one parameter, InputString, which is expected to be a string. The function return will also be a string. Below is the step by step guide on how to develop this function. You can find the completed function and test subroutine at the bottom.

Function ExtractNumbers(InputString As String) As String

End Function

Next we need to declare 3 variables.

Dim i As Integer
Dim Char As String
Dim ResultString As String

i is used as a loop counter. Char represents a single character in the string. ResultString will store the extracted numbers.

A For loop is used to iterate through each character in the inputString. The loop starts from the first character (1) and continues until the length of the string (Len(inputString)).

For i = 1 To Len(InputString)

Next i

Mid Function extracts a single character from the InputString at the position i and assigns it to the variable Char.

Char = Mid(InputString, i, 1)

Check this microsoft documentation to learn more about mid function.

Mid function

If Statement checks if the extracted character is numeric using the IsNumeric function.

If IsNumeric(Char) = True Then

End If

Want to learn more about IsNumeric Function? Then check this post.

IsNumeric Function

If the character is numeric, it is appended to the resultString

ResultString = ResultString & Char

Following line is used to return the extracted numbers as the result of the function.

ExtractNumbers = ResultString

Here is the full code of the ExtractNumbers function.

Function ExtractNumbers(InputString As String) As String

   Dim i As Integer
   Dim Char As String
   Dim ResultString As String

   'Loop through each character in the input string
   For i = 1 To Len(InputString)
     Char = Mid(InputString, i, 1)

     'Check if the character is a number
     If IsNumeric(Char) = True Then
       'Append the number to the result string
       ResultString = ResultString & Char
     End If
   Next i

   'Return the result string containing only numbers
   ExtractNumbers = ResultString

End Function

Now let's create a subroutine to use the ExtractNumbers function on a sample string. This subroutine initializes a string, calls the function, and prints the extracted numbers in the Immediate Window.

Sub TestExtractNumbers()

   Dim originalString As String
   Dim result As String

   'Example string with alphanumeric characters
   originalString = "ProductA123"

   'Call the function to extract numbers
   result = ExtractNumbers(originalString)

   'Display the result in the Immediate Window
   Debug.Print result

End Sub