Visual Basic for Application (VBA) is a fantastic tool for automating work in Excel. It allows us to program our work and customize it to our needs.
In VBA, we can create our functions and use them in our workbook to return a certain value or array of values. In the example below, we will do just that.
Insert VBA Function
The first thing that we need to do is to open the VBA. We do that by clicking ALT + F11, and then clicking on the Insert >> Module on the window that appears:
In the module, we will insert the following code:
|
1 2 3 4 5 6 7 8 9 |
Function ExtractNumbers(Cell As String) As Long Dim res As String Dim i, StrLength As Integer StrLength = Len(Cell) For i = 1 To StrLength If IsNumeric(Mid(Cell, i, 1)) Then res = res & Mid(Cell, i, 1) Next i ExtractNumbers = res End Function |
This is what the code looks like in the module itself:
This function is made to extract the numbers from our string. The first thing that we do is to declare a function, give it a name, define its parameters, and define what type the function will return.
Our function will be called “ExtractNumbers”, and will have one parameter (cell) that will be in a form of a string, and the function itself will return a number.
Next, we declare variables res as String, i and StrLength as Integer. StrLength will be equal to the number of characters in our cell.
Then we get to the For Next Loop:
|
1 2 3 |
For i = 1 To StrLength If IsNumeric(Mid(Cell, i, 1)) Then res = res & Mid(Cell, i, 1) Next i |
This loop goes through every character in our cell and checks if the character is a number. It saves all the numeric characters that are then stored as the result of our function.
Using VBA Function in Workbook
Now that we have our function, the next step is to use it in real-life experience. We will make a list of random addresses in our worksheet:
These addresses are different in terms that we have numbers in various positions (at the beginning of the text, in the middle, or at the end).
Next to column A, we will search for numbers in our cells with the defined function.
We will simply insert it as any other formula, calling it by its name. So this is what we will insert in cell B2:
|
1 |
=ExtractNumbers(A2) |
We will expand the formula to the end of the list, and these are the results we will end up with:
If we had numbers in different places in one cell, they would all get extracted.
We can also combine this formula with different formulas in Excel. When created, there is no difference between this formula and every other pre-existing formula in Excel.