Formula to match partial text string.

Robby87

Board Regular
Joined
May 9, 2008
Messages
128
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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


in the same method, how to get result from A1:E8, instead of A1:A8
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,725
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top