Check if cell in Excel worksheet has number using RegEx VBA

The isNumeric() function in VBA is often used to check if a cell has numbers in an Excel worksheet. However, there’s a super simple method to do this. You can use RegEx or Regular Expressions in Excel VBA to check if your worksheet cell has numbers.

Look at the image below.

Check if worksheet cell has numbers using RegEx

Each cell in the first column has alphanumeric values. I want to extract only the numbers from the values and show it in the next cell.

Add RegEx Reference in VBA

To use regex in VBA, you will have to add a reference of Regular Expression in your VBA editor.

• In the editor, select Tools from the top menu and choose References.

• Find Microsoft VBScript Regular Expressions 5.5 library, select it (or check it) and press OK.

Note: In addition to adding reference, you can also use CreateObject() method for RegEx. I have explained it here.

Macro

All set, now lets write the macro.

Option Explicit

Private Sub CommandButton1_Click()
    Worksheets("Sheet1").Range("B2:B10").ClearContents    '  Clear values in 2nd column.
    getNumbers (Worksheets("Sheet1").Range("A2:A10"))
End Sub

Sub getNumbers(oRange As Range)
On Error GoTo e1

    Dim rE As RegExp
    Set rE = New RegExp

    Dim myMatches As MatchCollection
    Dim myMatch As Match

    With rE
        .IgnoreCase = True
        .Global = True
        .Pattern = "(\d+)"    ' Assign a regex pattern.
    End With
    
    Dim myAccessary As Variant
    Dim iRow As Integer
    iRow = 1
    
    For Each myAccessary In oRange
        Set myMatches = rE.Execute(myAccessary.value)    ' Execute the string
        
        For Each myMatch In myMatches
            ' Debug.Print myMatch.value
            oRange.Cells(iRow, 2) = myMatch.value
            iRow = iRow + 1
        Next
    Next myAccessary
e1:
    '  show errors, if any.
    Debug.Print Err.Description
End Sub

I have a button control on my worksheet. Therefore, the macro executes upon button click event. You can ignore the button and write the macro in a module.

The macro extracts data from the 2nd column and loops through each row and cell in the given range and checks any cell in the 1st column has numbers.

The Regular Expressions

The RegEx pattern in this example is very simple.

rE.Pattern = "(\d+)"

• \d: back slash followed by a lowercase d, indicates number. Uppercase D checks for alphabets.

• + will extract all the numbers. Remove the "+" sign and see what result you get.

• The regex pattern "\d+" is inside braces ( ), which indicates a group, because I want the numbers to be displayed in a group, not individually.

Finally, it loops through each row in the range, executes the RegEx and looks for a match (that is, numbers).

You might also like this article: How to extract patterns from a string in Excel using RegEx VBA

Conclusion

You can use the isNumeric() function or the RegEx method that I have shown in this post, to extract numbers only from a cell in Excel. Both have distinct usages, therefore there is no point arguing which is best.

However, the macro in the previous post using "isNumeric()" is slightly shorter than the method using RegEx. Now you choose.

← PreviousNext →