Formula to match partial text string.

[Robby87]

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.

[RoryA]

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?

[Robby87]

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

[Robby87]

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?

[RoryA]

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?

[Robby87]

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.

[RoryA]

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).