extract numbers from text string

Posted on 2014-08-04
Last Modified: 2014-08-14
I have a table of data and one of the column contains a string.  The first set of charachters are always a number.  The second set of charachters are always letters.   I am trying to extract the first set of numeric charachters.  Using a simple left() function does not work becasue sometimes the numbers are one digit an sometimes they are two.  For example, the string may look like the following:




In the example above, I need to extract the '11' and the '1' respectively.

Thanks for your assistance.
Question by:mkobey
    LVL 35

    Expert Comment

    Will this work for you ?
    Sub test()
    Dim str As String
    str_in = "09s8asdf098sd0985"
    str_out = ""
    For i = 1 To Len(str_in)
        If IsNumeric(Mid(str_in, i, 1)) Then
            str_out = str_out + Mid(str_in, i, 1)
        End If
    Next i
    MsgBox str_out
    End Sub

    Open in new window

    There's also a formula instead:


    Open in new window

    assuming the string is in A1, and you put this formula in B1 (and finish with CTRL+SHIFT+ENTER, and sometimes regional settings make your formula use ; instead of , so change that if you see an error)
    One slight flaw though, this only detects one number sequence (in essence, returns 11 when cell A1 is 11AA, but returns N/A when cell A1 is 11AA11)
    LVL 27

    Accepted Solution

    You can find out the leftmost numeric characters with an array function.  

    If your first value is in cell A2, enter this array formula (using [Ctrl]+[Shift]+[Enter):

    It will produce the left-most digits only but as text, in case there are leading zeroes.  See the attached example file.

    LVL 31

    Assisted Solution

    by:Rob Henson
    Are they always just one or two digits?

    If so this will suffice:


    Where string of characters is in G30.

    Rob H
    LVL 27

    Expert Comment

    by:Glenn Ray
    If indeed there are always one or two leading digits and you want the number value, Rob's solution is best.  It's simple and faster than the array function I provided.

    If there could be more than two characters - or if you need leading zeroes - or if there are no numbers - the array function will work.


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now