Extract Numbers From A String In Excel

Hello Excellers, welcome back to another #MacroMonday blog post in my 2021 series. Today I will show you how to create your very own Excel Function. This Excel function will allow you to extract numbers from text strings automatically in Excel. So, if you need to do this regularly, this will save you a lot of time and effort. These functions are usually referred to as User Defined Functions.

Table of contents.

Example Data Set.
The VBA Excel Function Code.
Prepare To Write Your User Defined Function (UDF).
First Steps. Naming Your User Defined Function.
Declaring Any Variables.
Loop Through All Characters In The String CellReference.
The Function Result!. Easily Extract Numbers From A String In Excel.
Using Your New Function In Any Excel Workbook.

Example Data Set.

See my example below. I have to extract regularly the model type in an equipment list. Users have the same make of equipment, but the type or number of the model can be one of many. This self-written Excel function has multiple uses, which is as easy as a regular Excel function.

The VBA Excel Function Code.

Here is the code for this Function.

'Function by How To Excel At Excel.Com
Function ExtractNumbers(CellReference As String)
Dim StringLength As Integer
StringLength = Len(CellReference)
    For i = 1 To StringLength
        If IsNumeric(Mid(CellReference, i, 1)) Then Result = Result & Mid(CellReference, i, 1)
            Next i
            ExtractNumbers = Result
End Function

Prepare To Write Your User Defined Function (UDF).

Open the Visual Basic Editor. You can do this either by

hitting ALT+F11 or
hit the Developer Tab | Code Group | Visual Basic.

So, next, you need to insert a new Module to store your VBA function code. This is straightforward. Select the Insert Menu | Select Module to create a New Module.

First Steps. Naming Your User Defined Function.

Start the code by typing Function, then type the name of your UDF. I have chosen to name this Excel Function ExtractNumbers. We also need to set out the arguments that the Function will take. These are written straight after the Function name. Notice that Excel automatically put the End Function at the end of the code. Simply write the other lines of code between these two

Function ExtractNumbers(CellReference As String)
End Function

There is only one argument in the Function. It is the cell that contains the string to extract numbers.

Declaring Any Variables.

The next step in writing this Excel Function is to declare any variables. Declaring variables created a type of memory container in Excel’s memory to store these values. In this Function, I have created a variable StringLength. This holds the length of the string. I have declared that this value will be an integer.

Dim StringLength As Integer
StringLength = Len(CellReference)

Loop Through All Characters In The String CellReference.

The code loops through each character in the user-selected string passed to Excel in the Function argument. This part of the code is For i = 1 To StringLength….Next i.

It is in between these lines of code is where Excel will use the MID Function to extract a character from the cell at each iteration of the For Next loop.

 For i = 1 To StringLength
     If IsNumeric(Mid(CellReference, i, 1)) Then Result = Result & Mid(CellReference, i, 1)
            Next i

The MID Function Syntax is as follows.

=MID(text, start_num, num_chars)

Where text is the argument CellReference, start_num is the first character in the For Next Loop, and num_chars is 1. Only charcter at a time is to be returned from the CellReference string.

Excel uses the IsNumeric Function to determine if the extracted character is a number. Then, each extracted number combines to generate the result of the ExtractNumbers Function.

The Function Result!. Easily Extract Numbers From A String In Excel.

FInally the Result of the looped characters is displayed as a result of the Function.

ExtractNumbers = Result

The very last line of code is already inserted by Excel when the Function Name was created.

End Function

Using Your New Function In Any Excel Workbook.

To use this new Excel Function, regularly save the code in your Personal Macro Workbook. Then, use the macros saved in this location at any time you are in an Excel session. They will be available.