If you have worked on a dataset that contains numbers and strings, you may have realized there’s no in-built function that can be used to extract numbers from a string. Sometimes you may have a dataset that you need to extract numbers from its strings. Doing this manually may be tiresome and time-consuming. Thankfully, this article got you covered. We will discuss ways and workarounds that can be used to extract numbers from strings in Excel.
1] Using the SUMPRODUCT function
Below are the workarounds to follow:
1. Open the Excel application and workbook that contains the dataset you wish to extract numbers.
2. Click on the cell that will hold your results. Then, type the Equal sign and then type the SUMPRODUCT function. That is, =SUMPRODUCT (
3. Next, add this function:
=SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(–MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10)
The A5 in the formula represents the cell index of the cell that contains your string.
4. Finally, hit the Enter button, and the Number will be extracted and saved in the selected cell.
2] Using VBA to extract Numbers from String
Here are the steps to follow:
1. Open the Excel application.
2. Open the Workbook containing the worksheet with the strings.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Sub ExtractNumber()
Dim xRg As Range
Dim xDRg As Range
Dim xRRg As Range
Dim nCellLength As Integer
Dim xNumber As Integer
Dim strNumber As String
Dim xTitleId As String
Dim xI As Integer
xTitleId = "KutoolsforExcel"
Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
If TypeName(xDRg) = "Nothing" Then Exit Sub
Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
If TypeName(xRRg) = "Nothing" Then Exit Sub
xI = 0
strNumber = ""
For Each xRg In xDRg
xI = xI + 1
nCellLength = Len(xRg)
For xNumber = 1 To nCellLength
If IsNumeric(Mid(xRg, xNumber, 1)) Then
strNumber = strNumber & Mid(xRg, xNumber, 1)
End If
Next xNumber
xRRg.Item(xI) = strNumber
strNumber = ""
Next xRg
End Sub
6. Press the F5 key to run the code. In the KutoolsForExcel dialogue-box, select the cell that contains your string and hit OK button.
7. Next, select the cell where the results will be saved. That is all you need to do.
3] Using Kutools Tool
Here are the steps to follow while using this method:
1. Download and Install Kutools Features. Then, open the Excel application.
2. Open the workbook that contains the strings you need to extract numbers. Click on the cell that will hold the results.
3. Go to the Kutools tab on the Ribbon, and click the Kutools Functions drop-down button.
4. From the menu, hover the cursor over the Text button. Then, click the EXTRACTNUMBERS button.
5. In the dialogue box, type the cell index of the string in the Text section and hit the OK button.