Function ExtractNumber(cell As Range) As Long
Dim Num As String
For i = 1 To Len(cell)
If IsNumeric(Mid(cell.Value, i, 1)) Then Num = Num & Mid(cell.Value, i, 1)
ExtractNumber = Num
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.
Extract numbers only from text strings:
With Kutools for Excel’s EXTRACTNUMBERS function, you can quickly extract only numbers from the text string cells.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Using formula is an easy way for most of Excel user.
Select a blank cell that is adjacent to the list you want to extract number only, and type this formula =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10) (A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula. See screenshot:
Note: The result will be showed as 0 (zero) when there are no numbers in the string.
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 60 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, and this will not destroy your original data.
If you want to split the text string into separated text and number columns, Kutools for Excel’s Split Cells also can help you to solve this task.
After installing Kutools for Excel , please do as follows:
1. Select the text string that you want to split, and then click Kutools > Text > Split Cells, see screenshot:
2. In the Split Cells dialog box, select Split to Columns under the Type section, and then check Text and number from the Split by section, see screenshot:
3. And then click Ok button, select a cell to put the result in the popped out dialog box, see screenshot:
4. Then click OK button, and the text strings have been split into separated text and number columns as following screenshot shown:
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful: