# 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
``````

## 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.

### 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.

### 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.

### 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.

## 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.

<< 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 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