Md. Abdur Rahim Rasel
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
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.
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
- 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
- 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.
- Further, type the UDF in the selected cell. The UDF is,
=NUMBERVALUE(Strip(B5,TRUE))
- 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.
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
- 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)
- 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.
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
- 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.
- 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.
- 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.
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.
