How to Separate Numbers from Text in Excel VBA – 3 Methods

Quick View

Public Function SeparateText(Rng As Range, Number As Boolean) As String
Dim a As Long
Dim b As String
a = VBA.Len(Rng.Value)
For i = 1 To a
    b = VBA.Mid(Rng.Value, i, 1)
    If ((VBA.IsNumeric(b) And Number) Or (Not (VBA.IsNumeric(b)) And Not (Number))) Then
        SeparateText = SeparateText + b
    End If
Next
End Function

This is the sample dataset. To separate numbers from text:

Method 1 – Create a User Defined Function to Separate Numbers From Text

Step 1:

• Open a Module: go to

Developer → Visual Basic

• In the Microsoft Visual Basic for Applications – Separate Numbers from Text window, insert a module.

Insert → Module

Step 2:

• In the Separate Numbers from Text module, use the following code.

Public Function Strip(ByVal x As String, LeaveNumbers As Boolean) As Variant
Dim a As String, b As String, i As Long
    For i = 1 To Len(x)
        a = Mid(x, n, 1)
        If LeaveNumbers = False Then
            If a Like "[A-Za-b ]" Then b = b & a
            'False keeps Letters and spaces only
        Else
            If a Like "[0-9. ]" Then b = b & a
            'True keeps Numbers and decimal points
        End If
    Next i
Strip = Trim(b)
End Function

• Run the VBA: go to

Run → Run Sub/UserForm

Step 3:

• Create a User Defined Function (UDF). Select D5.

• Enter the UDF in the selected cell. The UDF is:

• Press ENTER and you will see 50 as the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Method 2 – Use the LEN Function in the VBA Code to Separate Numbers From Text

Step 1:

• Follow the steps described in method 1, to insert a new module and enter the VBA code.

Function GetNumber(CReferece As String)
Dim x As Integer
Dim i As Double
x = Len(CRefeference)
For i = 1 To x
If IsNumber(Mid(CReference, i, 1)) Then Result = Result & Mid(CRefence, i, 1)
Next i
GetNumber = Result
End Function

• Run the VBA:

Run → Run Sub/UserForm

Step 2:

• Select D5. Enter the GetNumber function in the selected cell:

• Press ENTER and you will see 0 as the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

 Method 3 – Using a For-Loop to Separate Numbers From Text in Excel VBA

Step 1:

• Insert a new module and enter the VBA code.

Public Function SeparateText(Rng As Range, Number As Boolean) As String
Dim a As Long
Dim b As String
a = VBA.Len(Rng.Value)
For i = 1 To a
    b = VBA.Mid(Rng.Value, i, 1)
    If ((VBA.IsNumeric(b) And Number) Or (Not (VBA.IsNumeric(b)) And Not (Number))) Then
        SeparateText = SeparateText + b
    End If
Next
End Function

• Run the VBA:

Run → Run Sub/UserForm

Step 2:

• Create a User Defined Function (UDF). Select D5.

• Enter the UDF in the selected cell.

• Press ENTER and you will see 50 as the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Things to Remember

•  You can open Microsoft Visual Basic for Applications window by pressing Alt + F11.
•  If the Developer tab is not visible on the ribbon, enable it. Go to

File → Option → Customize Ribbon

Download Practice Workbook

Download the practice workbook.

<< Go Back to Separate Numbers Text | Split | Learn Excel