How to extract numbers from mixed alphanumeric text strings in Excel?
Sometimes you may need to extract the numbers only from a list of alphanumeric strings. You can get it done with the following methods.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following VBA code can help you delete all nonnumeric characters from specified range and keep only numbers in cells. Please do as follows.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: remove all nonnumeric characters from cells
Sub GetNumbers() Dim xRegEx As Object Dim xRg As Range Dim xCell As Range Dim xTxt As String On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Pease select range:", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub Set xRegEx = CreateObject("VBScript.RegExp") With xRegEx .Pattern = "\D+" .IgnoreCase = True .Global = True End With xRg.NumberFormat = "@" For Each xCell In xRg xCell.Value = xRegEx.Replace(xCell.Value, "") Next Set xRegEx = Nothing End Sub
3. Then a Kutools for Excel dialog box pops up, please select the range with mixed alphanumeric text strings you need to keep only the numbers, and click the OK button.
Now all nonnumeric characters in selected range have been removed immediately, and only the numbers kept. See screenshot:
The above code will destroy the original data, if you want to keep the original data, here I can recommend you a useful tool – Kutools for Excel. With its EXTRACTNUMBERS function, you can quickly extract numbers from alphanumeric strings without remembering formula or running VBA code.
1. Select a blank cell for placing the extracted number, and then click Kutools > Functions > Text > EXTRACTNUMBERS. See screenshot:
2. In the Function Arguments dialog box, click the button to select the cell (says A2) which contains the alphanumeric text string you will extract the number, and then click the OK button.
Now all numbers in cell A2 are extracted. You can drag the Fill Handle down to extract all numbers of the left cells.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!