Results 1 to 9 of 9

Formula to match partial text string.

This is a discussion on Formula to match partial text string. within the Excel Questions forums, part of the Question Forums category; Wondering how I can go about this. Have a long list of bond names (full text string I want) and ...

  1. #1
    Board Regular
    Join Date
    May 2008
    Posts
    128

    Default Formula to match partial text string.

    Wondering how I can go about this.

    Have a long list of bond names (full text string I want) and another list that is compiled with essentially the same data, just more of it and unsorted. Is there a formula that is capable of matching the 3/4 text strings I have to the full text strings I have? the 3/4's are not "closest match" they should link up exactly to a full string in other cells.

    For example, A1:A2800 = full text strings (columns b, c, d have other data) E1:E500 has the 3/4 text strings that I want to reconcile with the full ones. Want to lookup the full text string, find the 3/4 string tha matches and then have the corresponding data to the 3/4 strings pulled out.

    let me know if you need more clarity.

    Thanks in advance.

    Rob.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    ex machina
    Posts
    21,354

    Default Re: Formula to match patial text string.

    It's not clear to me whether you want to look up the 3/4 strings in the table of full strings, or vice versa?
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  3. #3
    Board Regular
    Join Date
    May 2008
    Posts
    128

    Default Re: Formula to match patial text string.

    Essentially what I am trying to do is match the 3/4 strings to the appropriate full string. Full strings have the company name i.e., ONT BND 5.15 DEC1/15, while the 3/4 strings only contain BND 5.15 DEC1/15.(for example)

    worksheet with full strings has pricing from my bank, 3/4 strings have pricing from an outside dealer. is it possible to reconcile the 3/4 strings to the full strings and then have the output be the row (row across from 3/4 string) that corresponds to the selected 3/4 string?

    (hopefully this didn't drive you batty.)


    Rob

  4. #4
    Board Regular
    Join Date
    May 2008
    Posts
    128

    Default Re: Formula to match patial text string.

    Maybe I should clarify my objectives... I don't want someone to write me a magnificent formula or anything of the like... I am here for general knowledge, looking for a formula that can match partial text strings to full ones. Is this even possible to do in excel? What would be the best formula to apply here?

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    ex machina
    Posts
    21,354

    Default Re: Formula to match patial text string.

    If you are looking up the partials against the full strings, then you can use wildcards with MATCH:
    =MATCH("*"&E1&"*",$A$1:$A$1000,0)
    to return the matching row number. Does that help?
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  6. #6
    Board Regular
    Join Date
    May 2008
    Posts
    128

    Default Re: Formula to match patial text string.

    Just to try and firm this up.. I am trying to find the matching full string based on the partial text string. I think that is what you proposed here, but just in case I thought I would confirm my objectives.

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    ex machina
    Posts
    21,354

    Default Re: Formula to match patial text string.

    Yep - the formula above will return the row number where your partial string is found in the list of full strings (if there's more than one match, it will return the first one - I don't know if that's an issue).
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  8. #8
    New Member
    Join Date
    Jun 2013
    Posts
    1

    Default Re: Formula to match patial text string.

    I am new here, so I hope this POST is okay... I have a follow-up question to this Post:
    This is REALLY SUPER, but is there a way to have it return more that just the first one? Can I get a list of rows in which this partial string appears?

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    ex machina
    Posts
    21,354

    Default Re: Formula to match patial text string.

    You can use a formula like this:

    =IF(COUNTIF($A$1:$A$8,"*"&$E$1&"*")>=ROWS($E$1:$E1),SMALL(IF(ISNUMBER(SEARCH($E$1,$A$1:$A$8)),ROW($A$1:$A$8),""),ROWS($E$1:$E1)),"")

    where A1:A8 is the data range, E1 contains the text you want to check for, and the formula is entered in row 1. It needs to be array-entered using Ctrl+Shift+Enter rather than just enter as it is an array formula, then fill down until it returns a 'blank' cell
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com