How to create User define function to Separate text & Numbers in Excel VBA

Welcome to Excel Avon

User define function to Separate text & Numbers in Excel VBA

DOWNLOAD THE USED EXCEL FILE FROM HERE>>

In today’s post we will explain How to create User define function to Separate text & Numbers in Excel VBA, As you all know that many times while working with Microsoft Excel, we often have to separate text and numbers from some data in Excel. If we do it manually our time will be wasted but we can do it easily using VBA macros. Applying VBA code to separate numbers from text is an easy task. So today in this article, I will explain How to create User define function to Separate text & Numbers in Excel VBA, step by step with codes. 

And If we write any formula in a cell it suggests, we wrote =te in the cell all matches will be visible in the dropdown, now if we create any function in VBA then it will also show in the list. but parameters will not be suggested, whereas excel will suggest the parameters of the formula be existing in the system.

To understand you, we will take some data so that we can explain to you, I am using random data. Then I will add the function created in the text cell and select the range, then we will see the data separated like this.

Our User define function will be ready like this as you can see.

Create User define function to Separate text & Numbers in Excel VBA

User define function to Separate text

As we taught you how to insert a module when we were working with module, as we have learned in many posts so far, you can insert module. Let’s understand, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.

On opening in VBE, you have to go to Insert and then module has to be inserted, as can be seen in the image.

Once we insert the module, we will write a subroutine as public. if the procedure is public, it means you will be able to view them from within the excel sheet. They can be called from anywhere within your VBA project.

If subroutine is private, they can’t be seen in the excel macro window. and they are only available to use within that module. also, we will declare variables Inputtext as variant.

Public Function GetOnlyText(InputText As Variant)
End Function

Now we will declare I as Long.

Public Function GetOnlyText(InputText As Variant)
Dim i As Long
End Function

Then i = 1 range declare, and text will be input.

Public Function GetOnlyText(InputText As Variant)
Dim i As Long
For i = 1 To Len(InputText)
Next i
End Function

Now we store string and mid function in GetOnlyText. Write the parameters of mid function.

Public Function GetOnlyText(InputText As Variant)
Dim i As Long
For i = 1 To Len(InputText)
     GetOnlyText = GetOnlyText & Mid(InputText, i, 1)
Next i
End Function

Now we will add condition for extract Text, Mid function will extract data from given Input text like (a to z) we will copy the condition as paste it by applying or. again mid function will extract data from given input text like( A toZ)

Public Function GetOnlyText(InputText As Variant)
Dim i As Long

For i = 1 To Len(InputText)
    If Mid(InputText, i, 1) Like "[a-z]" Or Mid(InputText, i, 1) Like "[A-Z]" Then
        GetOnlyText = GetOnlyText & Mid(InputText, i, 1)
    End If
Next i
End Function

Now we will use the GetOnlyText Function in the cell.

Now the text has been extracted from the input text.

User define function to Numbers

Now we’ll copy text extract formula and paste it below. wherever there is text word, it will be replaced with Number. to Numbers type 0-9.

Public Function GetOnlyNumber(InputText As Variant)
Dim i As Long

For i = 1 To Len(InputText)
    If Mid(InputText, i, 1) Like "[0-9]" Then
       GetOnlyNumber = GetOnlyNumber & Mid(InputText, i, 1)
    End If
Next i
End Function

Now we will use the GetOnlyNumber Function in the cell.

Now the Number has been extracted from the input text.

Our User define function will be ready like this as you can see and function also working.

Therefore, I hope that you have understood How to create User define function to Separate text & Numbers in Excel VBA, maybe if you do not understand anything, then you can comment us with the question, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.

DOWNLOAD THE USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about How to create User define function to Separate text & Numbers in Excel VBA