Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Extract number only from strings with array formula

Extract number only from strings with Kutools for Excel

Split text string into text and number columns individually with Kutools for 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!


arrow blue right bubble Extract number only from strings with array formula


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.


arrow blue right bubble Extract number only from strings 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 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.

Click to know more about this EXTRACTNUMBERS feature.

Click to Download and free trial Kutools for Excel Now!


arrow blue right bubble Split text string into text and number columns individually with Kutools for Excel

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:

Click to Download and free trial Kutools for Excel Now!


arrow blue right bubble Extract number only from strings with Kutools for Excel

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!


Relative Articles:



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    xlViki · 4 years ago
    @jb: Use this UDF:


    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)
    Next i

    ExtractNumber = Num

    End Function
  • To post as a guest, your comment is unpublished.
    jb · 4 years ago
    this formula
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

    worked great, for any number under 4 digital but I have numbers over 1000 that I need to extract - any advice??

    thanks :)
  • To post as a guest, your comment is unpublished.
    jb · 4 years ago
    Thank you thank you!

    have been trolling the web for answer to simply extra numbers from string in excel and after many overbearing, confusing formulas yours worked like a treat!!

    much appreciated :D
  • To post as a guest, your comment is unpublished.
    Georgios · 4 years ago
    Great formula...very helpful!
    But, could you please advise what can I do with chinese characters? I guess it considers them as figures. Any idea?
  • To post as a guest, your comment is unpublished.
    Muksharna G · 4 years ago
    This formula fails with decimal places. e.g. "1.5 grams of abc" returns "15".