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

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


Download Practice Workbook

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


3 Suitable Ways to Separate Numbers from Text in Excel VBA

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

Read More: How to Extract Numbers after a Specific Text in Excel (2 Suitable Ways)


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

Read More: How to Extract Specific Numbers from a Cell in Excel (11 Ways)


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

Read More: VBA to Remove Numbers from String in Excel (4 Methods)


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


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo