# Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

## Pages

### 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

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.

Name

Email *

Message *