How to Separate Numbers from Text in Excel VBA (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with large Microsoft Excel, we often have to separate text and numbers from some data in Excel. We can easily do that by using the VBA Macros. Applying a VBA code to separate numbers from text is an easy task. Today, in this article, we’ll learn three quick and suitable ways to separate numbers from the text by applying the VBA code in Excel effectively.


Excel VBA to Separate Numbers from Text (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

separate numbers from text in excel vba


How to Separate Numbers from Text in Excel VBA: 3 Suitable Ways

Let’s say, we have a dataset that contains information about several data in column B. We want to separate numbers from the text by using the VBA code and also creating a User Defined Function (UDF) in VBA Code. Here’s an overview of the dataset for today’s task.

separate numbers from text in excel vba


1. Create a User Defined Function to Separate Numbers From Text

Now I’ll show how to separate numbers from text in Excel by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will separate numbers from text. Let’s follow the instructions below to separate numbers from the text!

Step 1:

  • First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Create an User Defined Function to Separate Numbers from Text in Excel VBA

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Separate Numbers from Text will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

  • Hence, the Separate Numbers from Text module pops up. In the Separate Numbers from Text module, write down the below VBA
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

Create an User Defined Function to Separate Numbers from Text in Excel VBA

  • Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 3:

  • After running the code, you will be able to create a User Defined Function (UDF). Hence, select cell D5.

Create an User Defined Function to Separate Numbers from Text in Excel VBA

  • Further, type the UDF in the selected cell. The UDF is,
=NUMBERVALUE(Strip(B5,TRUE))

Create an User Defined Function to Separate Numbers from Text in Excel VBA

  • After typing the UDF, simply press ENTER on your keyboard and you will get 50 as the output of the User Defined Function (UDF).

  • Now, autoFill the UDF to the rest of the cells, as a result, you will be able to separate numbers from the text which has been given in the below screenshot.

Create an User Defined Function to Separate Numbers from Text in Excel VBA


2. Use LEN Function in VBA Code to Separate Numbers From Text

Now, from our dataset, we will separate numbers from the text by using the LEN function in VBA Code. Undoubtedly, this is an easy and time-saving task. Let’s follow the steps below to separate numbers from the text!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to separate numbers from the text. The VBA code is,
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

Use LEN Function in VBA Code to Separate Numbers from Text

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the code, first, select cell D5. Further, type the GetNumber function in the selected cell. The GetNumber function is,
=GetNumber(B5)

Use LEN Function in VBA Code to Separate Numbers from Text

  • After that, press ENTER on your keyboard and you will get 0 as the output of the function.

  • Now, autoFill the GetNumber function to the rest of the cells, as a result, you will be able to separate numbers from the text which has been given in the below screenshot.

Use LEN Function in VBA Code to Separate Numbers from Text


3. Use a For-Loop to Separate Numbers From Text in Excel VBA

In this method, we will use the For loop to separate numbers from text. This is an easy and time-saving task also. Let’s follow the steps below to separate numbers from the text!

Step 1:

  • Insert a new module according to method 1, and type the below VBA code to separate numbers from the text. The VBA code is,
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

Use a For-Loop Separate Numbers from Text in Excel VBA

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the code, you will be able to create a User Defined Function (UDF) named Hence, select cell D5.

Use a For-Loop Separate Numbers from Text in Excel VBA

  • Further, type the UDF in the selected cell. The UDF is,
=SplitText(B5,TRUE)

  • After typing the SplitText, simply press ENTER on your keyboard and you will get 50 as the output of the SplitText function.

Use a For-Loop Separate Numbers from Text in Excel VBA

  • Now, autoFill the SplitText function to the rest of the cells, as a result, you will be able to separate numbers from the text which has been given in the below screenshot.

Use a For-Loop Separate Numbers from Text in Excel VBA


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to separate numbers from text with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo