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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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!