# 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:
`=NUMBERVALUE(Strip(B5,TRUE))`

• 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:
`=GetNumber(B5)`

• 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.
`=SplitText(B5,TRUE)`

• 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

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF