Find Text in String and return the values after that text with excel

Posted on 2012-08-31
Last Modified: 2012-09-07
Hello Experts,

I am trying to find a specific piece of text in excel.  The workbook has a column that has a long string of text in each row(A5:A10000). I  want to return the text that is located after the first search. e.g( this is the long string ID=135 test test tdasl;kj )  I would like to find the text" ID=" and return the number 135 after it.  

Is there a function that could help me do this?

Hope this makes sense.
Question by:skyzipper
    LVL 50

    Accepted Solution

    Is it always 3 digits? If so try this formula in B5 copied down


    or for a variable number of digits (1 to 5) try


    If you double-click on the fill-handle of B5 (bottom right of the cell) then that will fill the formula down automatically as far as you have continuous data in column A

    regards, barry
    LVL 19

    Expert Comment

    by:Ken Butters
    This solution assumes that you have a space after the ID=XXXX

    =MID(A1,FIND("ID=",A1)+3,FIND(" ",A1,FIND("ID=",A1))-3)
    LVL 24

    Expert Comment

    You could use the Data > Text to columns > and use 'space' and '=' as delimited.
    This will split the text into nice convenient columns.
    LVL 44

    Expert Comment

    If you wrap the VB Val() function in a public user defined function (like the StringVal function below, you can simplify your parsing and value extraction.
    Option Explicit
    Public Function StringVal(parmString As String) As String
        StringVal = Val(parmString)
    End Function

    Open in new window

    In a formula, it would look something like this:
    LVL 18

    Expert Comment

    To know possible "gotchas" that you have to watch out for while using Val function, see


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now