How to extract text from another text string

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation.

Example:

Suppose that cell A1 has the text string:

“Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function:

=LEFT(A1,5)

The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function:
=RIGHT(A1,11)

The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function:

=MID(A1,10,7)

The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions:

=LEFT(A1,(FIND(” “,A1)-1))

The result is “Creativity”.

The FIND function here will give us the position of the first SPACE. We subtract 1 to get the length of the first word. That length is used as a parameter for the LEFT function.

Comments

  1. regs says:

    Hi I need assistence with this situation.

    In column “B” I have a series of quetions, e.g “WHAT IS THE REPORT NAME? WHAT IS THE REPORT PATH? WHAT IS THE PROMPT ANSWERS THE USER USED?”.

    I need to extract every quetion to a different column, can this be done?
    Column “C” should be “WHAT IS THE REPORT NAME?”
    column “D” should be “WHAT IS THE REPORT PATH?”
    column “E” should be “WHAT IS THE PROMPT ANSWERS THE USER USED?”

  2. admin says:

    Hi,

    Try this (Assuming we are in Row 1):

    C1: =LEFT(B1;(FIND(“?”;B1)))
    D1: =MID(B1;LEN(C1)+2;FIND(“?”;B1;LEN(C1)+2)-(LEN(C1)+1))
    E1: ==RIGHT(B1;LEN(B1)-LEN(C1)-LEN(D1)-2)

    Note that I use “;” as a separator for parameters. Some installations use “,”.

    I have tested this in Excel 2007, but I guess the functions are the same across different Excel versions.

    Regards.

  3. mobycane says:

    Hi there

    Supposed I have a string of text like this in A1:

    CATHY 750 0 -120,000,000 JPY

    May I know the formula to extract the figure “-120,000,000″ from the above string?

    Regards

  4. admin says:

    Assuming that the string is in cell A1 and the length of the text to be extracted is fixed then you can try this formula:

    =MID(A1;FIND(“-”;A1)+1;11)

    The FIND function will locate the position of the “-”. We add 1 to get the start position of the required string (the number 12,000,000,000). The MID function will then extract the number.

    Note: You may have to use “,” instead of “;” in the formula depending on your installation.

  5. Marshall says:

    Hello,

    I have a comments field that has 60+ characters. Somewhere in the cell, there are the letters “BL” or “NO” or “NO.” There is a string of letters and numbers after BL/ NO/ NO. and the length of this string varies, but they will always be followed by a space. Is there a way to do it?

    Thanks,

  6. admin says:

    I don’t think there is an easy way, however you can try this formula (assuming your string is in A12):

    =MID(A12;FIND(“BL”;A12)+2;((FIND(” “;A12;11))-(FIND(“BL”;A12)+2)))

    Here I am assuming that you know in advance that the letters are “BL”. If not then this may require another complex formula to decide what the letters are, and probably put them in a hidden column.

  7. Jean-Yves says:

    Hi there,

    Supposed I have a text in a cell that is very long, but there is one sentence that I would like to keep and delete the remainder of the text. That sentence always start with the same 3 words and keep the whole sentence until the first period is met.
    For ie, anythinggjljksdlkjfalskjfdaljf. fkljsdlfjslfkjsdalk For a complete list of patch download links, please refer to Microsoft Security Bulletin MS11-002 (http://www.microsoft.com/technet/security/bulletin/MS11-002.mspx).,
    dljsdlkjfsldjflsdjf dsfljdsfljsdflj

    sdflsdjffsd

    So here just keep ” For a complete list of patch download links, please refer to Microsoft Security Bulletin MS11-002 (http://www.microsoft.com/technet/security/bulletin/MS11-002.mspx).” and delete the text before and after in that cell.

    What would be the VBA code for it?

    • admin says:

      Option Explicit
      Function ExtractText(myRange As Range)
      Dim iStart As Variant, iEnd As Variant, ilength As Variant, iIgnore As Variant
      ilength = Len(myRange)
      iStart = WorksheetFunction.Find("For a complete list", myRange)
      iEnd = WorksheetFunction.Find("mspx", myRange)
      iIgnore = ilength - iEnd - 6
      ilength = Len(myRange) - iStart
      ExtractText = Mid(myRange, iStart, (ilength - iIgnore))
      End Function

      Create a user-defined function using the code above, then use it in your worksheet as follows (assuming that your text is in cell A3):

      =ExtractText(A3)

  8. Javy says:

    From the string below, how can I pull the value of Joe Smith?

    The user account CN=Joe Smith,OU=B11111,OU=city state,OU=XXXX Migrated Sites,OU=United States,DC=xxxxxxx,DC=xxx was disabled.

    • admin says:

      Assuming that your text is in cell A1, use this formula:

      =MID(A1;FIND("CN=";A1)+3;FIND(",";A1)-FIND("CN=";A1)-3)

      You may have to change “;” to “,” depending on your installation.

  9. I’m trying to extract “ENG-085″ into one row (c2), and EQV-ENG085 in to the next row (b3) (and also copy/paste psy-140 ). Then I’d like the next part of the string (triggered by ‘take’ ) to extract ENG-090 and paste into the next column (column C). So, ENG-090 would be in column C2. Any ideas on how to get this workin.g.?’

    row 1 A B
    row 2 PSY-140 # Take ENG-085 ENG-110 or EQV-ENG085; # Take ENG-090 EQV-ENG090 or ENG-131;

    • psy-140 is in the first column (a),
      #take…etc. is in the next column (b)

      • admin says:

        Here is a general rule to apply with any text:
        To extract text you use the MID function: MID(mytext;start position; the length of text to extract).
        mytext is known : B2
        Length is known : 7
        What is missing is the start position. To get this we use the FIND function:
        FIND(“ENG-085″;B2)
        Finally , we substitute the length of the string with the FIND function as follows:
        =MID(B2;FIND(“ENG-085″;B2);7)

        You can then apply the same rule to other strings.

  10. Vidyadhar says:

    Hi,
    I have a text string something like this –
    • Issue start date: 09-06-2011 • When does the customer get the error? : anytime • Which Handset is the customer using?: Samsung Galaxy Tab • Had customer recently begun using a different handset when the issue started?: No, always used the same device • Where is the issue: Indoors and Outdoors both • Affected postcode: AB1 2CD • Post code of area where service works fine: PQ1 2XY

    What I want to do is extract the text “AB1 2CD” i.e. after text string ‘Affected postcode:’ in the next cell….

    How can we do this?

    • admin says:

      Assuming your text is in cell A2, use this formula:

      =MID(A2;FIND(“Affected postcode:”;A2)+19;7)

      Please see the reply to Angel Fonseca above to understand the logic behind this formula.

  11. Valérie says:

    Hi,

    Suppose I have a column like:

    A1= Metadatascripts
    A2= SACDATA
    A3= Documentation
    A4= SQLDEMSYSAC

    How can I get the answer ‘Yes’ or ‘No’ in the cell B1 to the question: does the string “SAC” occures in column A?
    In the example the answer has to be ‘Yes’, because we’ve got the string SAC in cell A2 and A4.

    • admin says:

      Use this formula in B1:

      =IF(COUNTIF(A1:A4;"*SAC*")>0;"YES";"NO")

      1. The inner function COUNTIF(A1:A4;”*SAC*”) will count all occurrences of the letters “SAC”. The “*” means look for the letters “SAC” anywhere within the cell.
      2. The outer function IF will evaluate the result of COUNTIF , and if the result is > 0 it will put “YES” in cell B1, otherwise “NO”.

  12. Andrew says:

    Hi,

    Assuming I have in row 1 : Names (Code)
    The names are of different length, but the code is 3 letters. Though sometimes there are 3 letters preceding the name (Company linked)

    Examples of names : CNN Newyork (NYK), CNN Colombo (COO), Alantis (ALA), Hogwarts(HOG). (Different formating too)
    How do I split the names into different rows?

    IE :
    Row 2 : CNN Newyork, CNN Colombo, Alantis, Hogwarts
    Row 3 : NYK, COO, ALA, HOG

    Thanks!

    • admin says:

      Suppose your text is in cell A1, and you want to extract the code to B1.
      Use this formula in B1:

      =MID(A1;FIND("(";A1)+1;3)

      Autofill for the rest of the rows.

      The FIND will give us the position of the “(” which indicates the start of the code. We add 1 to get the position of the code itself; We feed this into the MID function to get the code.

      • Telconap says:

        Hi Guys,
        Sorry to jump in but i have a question
        i have – My Name is John (Mr) in one cell, Alias Magie (Mrs) and i wanted to know how i can get only the MR/MRS extracted???
        Please help
        Thanks for your reply
        Telconap

      • admin says:

        This formula will look for either of the words “Mr” or “Mrs” in cell A1 and then it puts the value in the cell where the formula is.

        =IF(MID(A1;FIND(“Mr”;A1);3)=”Mrs”;”Mrs”;”Mr”)

        I prefer that you type the formula through the keyboard because the quotes sometimes get messed up with copy / paste.
        Also you may have to use “,” instead of “;”.

  13. telconap says:

    Hi Admin
    Dude you are a great man
    Thanks for the formula, i had only to use (,) instead of(;)
    help pls again :(
    i have some name in cell a1,a2,a3:
    Alsson Magie,
    John river Ferdo,
    Jr-fred Cossi
    How can i seperate the name like: cell a4 Alsson,John,Jr-Fred and in cell a5 Magie, Ferdo Cossi..
    Appreciate your help dude,

    • admin says:

      1. For the left side use this formula:

      =MID(A1;1;FIND(" ";A1)-1)

      2.For the right side use this formula:

      =RIGHT(A1;LEN(A1)-FIND(" ";A1))
      The second one is tricky though because if the full name consists of three names then the right part could have 2 names.

  14. Georgia says:

    How can you combine text manipulation to extract the middle numbers of the following example and then copy it down to extract the middle numbers only of the rest of the data in the column:

    dyak-23+my (extract the 23 only)

    Then copy down to extract the following:

    ary-412+zzxt (extract only the 412)
    pg-9075+xrw (extract only the 9075)

    • admin says:

      Assuming the text is in cell A1 then use this formula:

      =MID(A1;FIND("-";A1)+1;FIND("+";A1)-FIND("-";A1)-1)

      You can then use autofill for the rest of the cells.

  15. LazyArcher says:

    Hi there,

    I need your help to extract a couple of lines from a cell. For the example (shown below) I need to extract ticket number and IP Addresses listed to a new worksheet in such a way that the worksheet captures the IP addresses for the given ticket number
    something like…
    Ticket Number IP Address
    XXXXXXXXXXX XX.XX.XX.XX
    YY.YY.YY.YY
    ZZ.ZZ.ZZ.ZZ

    Example:
    “The following report has been generated and IP address have been listed.

    Ticket number: XXXXXXXXX

    IP Address: XX.XX.XX.XX
    YY.YY.YY.YY
    ZZ.ZZ.ZZ.ZZ”

    How can achieve this with formula?

    • admin says:

      Assuming that the text is in cell A1 and the length of the Ticket number is 9 digits and the IP Address is 11, use this formula for Ticket number:
      =MID(A1;FIND(“Ticket number:”;A1)+14;9)
      And this one for IP Address:
      =MID(A1;FIND(“IP Address:”;A1)+12;11)
      Adjust field length if necessary.

  16. Albert says:

    I have these data in one cell:

    Jefferson Lane 25
    Dillinger 88
    Park Avenue 22
    Winchester Street 45

    I would like to parse the sell and extract the street number and place it in a separate cell. The Text To Columns feature won’t do the trick because of the spaces contained in the names of some of the streets.

    Any ideas ?

    • admin says:

      Because there is no common criteria in the street name/number to use in extracting it, The only way I can think of is to have a delimiter before and after the street like a “,” or “-”.

  17. Gareth says:

    I’d really like to know a formula to look for specific text within other text, and if found display that text. The text items in the cell are always separated by commas, but there other irrelevant text items in the same cell.

    Example:
    column B to look to see whether column A contains “red”, or “Bill and Ben”, or “big” and display all of these words found.
    A1: red,green,blue,black,Bill and Ben,Flower Pot Men,big,small
    B1: red,Bill and Ben,big

    A2: red,white,green,blue,orange,black,Flower Pot Men,big,small
    B2: red,small

    A3: white,green,blue,purple,black,Flower Pot Men,big,medium,short
    B3: big

    It is important that column B maintains the separation of words by commas.

    If it’s not possible to pull out the words I need, perhaps it’s possible to exclude/delete the other unnecessary words?

    Many thanks for any help!