# How to Extract Number from a string in Excel

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 (

=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:

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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.