Skip to main content

How to extract number only from text string in Excel?

Author: Sun Last Modified: 2024-12-11

When working with Excel, you might encounter situations where you need to extract numbers from text strings. Whether the numbers are at the beginning, end, or embedded within the text, Excel provides various methods to achieve this. This guide will explore various methods to handle different scenarios, including extracting decimal numbers, numbers from specific positions, or numbers from the start or end of a string.

Extract number from any position from text string

Extract decimal number only from text string

Extract number from the end of text string

Extract number from the beginning of text string


Extract number from any position from text string

To help you extract numbers from any position within a text string, we’ll introduce several practical and efficient methods.

♦ Extract number from any position from text string with formulas

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

Select a blank cell where you want to output the extracted number, then type any of the below formula, then drag the formula to other cells. See screenshot:

● All Excel versions:

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

● Excel 365 or Excel 2021 and later versions:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))

apply formulas to extract number from text strings


♦ Extract number from any position from text string with formulas

Kutools for Excel offers a quick and user-friendly way to extract numbers from text strings without remembering any formula, helping you save time and streamline your workflow. Here’s how to do it.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...
  1. Click Kutools > Text > Extract Text, see screenshot:
    Click Kutools > Text > Extract Text
  2. In the Extract Text dialog box, do the following operations:
    (1.) Select the data list you want to extract numbers;
    (2.) Choose Extract the number option;
    (3.) Check Insert as a formula option as you need;
    (4.) Finally, click OK button.
    specify the options in the dialog box
  3. In the following prompt box, select a cell to output the extracted result, see screenshot:
    select a cell to put the result
  4. Click OK, all numbers from each cell are extracted at once. See screenshot:
    all numbers are extracted by kutools

♦ Extract number from any position from text string 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:
    vba code to select the text strings
  4. Then, click OK, another prompt box is following, please select a cell to output the result, see screenshot:
    vba code to select a cell to put the result
  5. At last, click OK button, and all numbers in the selected cells have been extracted at once.

Extract decimal number only from text string

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.

Copy and paste any of the following formula to a blank cell, and then fill handle down to the cells that you want to contain this formula, all the decimal numbers are extracted from the text strings

● All Excel versions:

=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))

● Excel 365:

=REGEXEXTRACT(A2,"[\d.]+")

apply formulas to extract decimal number from text strings


Extract number from the end of text string

When working with text strings in Excel, you may need to extract numbers located at the end of the string. To isolate numbers at the end of a string, the following formula can help you.

Copy and paste the below formula into a blank cell, press Ctrl + Shift + Enter to get the first result:

=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))

Then, drag the fill handle down to fill this formula to other cells. This formula extracts numbers exclusively from the end of the text string, ignoring any numbers that appear at the beginning or in the middle. See screenshot:

apply formula to extract number from the end of text strings


Extract number from the beginning of text string

To extract numbers from the beginning of a text string, please use the following formula.

Copy and paste the below formula into a blank cell, press Ctrl + Shift + Enter to get the first result:

=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)

Then, drag the fill handle down to fill this formula to other cells, only the starting number is extracted. See screenshot:

apply formula to extract number from the beginning of text strings

Extracting numbers from text strings is a common task in Excel, and there are several effective methods to achieve this, depending on your needs and the complexity of your data. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.


Relative Articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!