Look at the image below.
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.
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 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.
That’s it. ☺