## How to extract number only from text string in Excel?

Have you ever wanted to extract numbers only from a list of string in Excel? Here I introduce some ways for you to extract only numbers quickly and easily in Excel.

Method 1: Extract number only from text strings with formula

Method 2:Extract number only from text strings with VBA code

Method 3: Extract number only from text string with Kutools for Excel

Method 4: Extract decimal number only from text string with formula

#### ** Method 1: Extract number only from text strings with formula**

The following long formula can help you to extract only the numbers from the text strings, please do as this:

Select a blank cell where you want to output the extracted number, then type this formula: **=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)**, and then drag the fill handle to fill the range you need to apply this formula. See screenshot:

**Notes:**

- 1.
stands the first data you want to extract numbers only from the list.**A5** - 2. The result will be showed as 0 when there are no numbers in the string.

**Extract numbers only from text strings:**

With** Kutools for Excel**’s** EXTRACTNUMBERS** function, you can quickly extract only numbers from the text string cells. ** Click to download Kutools for Excel!**

#### ** Method 2: Extract number only from text strings with VBA code**

Here is a VBA code which also can do you a favor, please do as follows:

**1**. Hold down the **Alt + F11** keys to open the **Microsoft Visual Basic for Applications** window.

**2**. Click **Insert** > **Module**, and paste the following code in the **Module** Window.

**VBA code: Extract number only from text string:**

```
Sub ExtrNumbersFromRange()
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
```

**3**. And then, press **F5** key to run this code, and a prompt box is popped out to remind selecting the text range you want to use, see screenshot:

**4**. Then, click **OK**, another prompt box is following, please select a cell to output the result, see screenshot:

**5**. At last, click **OK** button, and all numbers in the selected cells have been extracted at once.

#### ** Method 3: Extract number only from text string with Kutools for Excel**

**Kutools for Excel** also has a powerful function which is called **EXTRACTNUMBERS**, with this function, you can quickly extract only the numbers from the original text strings.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |

After installing **Kutools for Excel**, please do as follows:

**1**. Click a cell besides your text string where you will put the result, see screenshot:

**2**. Then click **Kutools** > **Kutools functions** > **Text** > **EXTRACTNUMBERS**, see screenshot:

**3**. In the **Function Arguments** dialog, select a cell which you want to extract the numbers from the **Txt** text box, and then enter **true** or **false** into the **N** text box, see screenshot:

**Note**: the argument **N** is an optional item, if you enter **true**, it will return the numbers as numerical, if you enter **false**, it will return the numbers as text format, the default is false, so you can leave it blank.

**4**. And then click **OK**, the numbers have been extracted from the selected cell, then drag the fill handle down to the cells you want to apply this function, you will get the following result:

** Click to Download and free trial Kutools for Excel Now! **

#### ** Method 4: Extract decimal number only from text string with formula **

If the text strings which including some decimal numbers in your worksheet, how could you extract only the decimal numbers from the text strings?

The below formula can help you to extract the decimal numbers from the text strings quickly and easily.

Enter this formula：**=LOOKUP(9.9E+307,--LEFT(MID(A5,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A5&"1023456789")),999),ROW(INDIRECT("1:999"))))**，and then fill handle down to the cells that you want to contain this formula, all the decimal numbers have been extracted from the text strings, see screenshot:

#### ** Extract number only from strings with Kutools for Excel**

**Kutools for Excel**: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

**Download and free trial Now!**

**Relative Articles:**

### Best Office Productivity Tools

**Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. ** **Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...**

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!