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.

 

[FKemps]

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?

This formula is helpful for me too, but I would like to look up the full strings against the partials, so the other way around...
How would this be made possible?

In the end I would like to have an abbreviation of the partial (in the column beside it) returned when the match between each string in a list of full strings is made with an array of summaries (partials) of all these full strings.

Example:
Equipment description list (table 1) (full strings)
hand operated valve to protect vacuum pumps
hand operated vent valve
hand operated valve to TRV
hand operated drain valve

Partial description list (typical; table 2) (partial)
drain
vent
hand operated valve

With next to it (in table 2) the three corresponding abbreviations (abbrvs)
DRN
VNT
HOV

In table 1 I want a cell returning the correct abbreviation next to the full string, having matched both via the partial, so:
hand operated valve to protect vacuum pumps | HOV
hand operated vent valve | VNT
hand operated valve to TRV | HOV
hand operated drain valve | DRN

If anyone could help me out on the combination of these two challenges I would me most grateful!

 

[bjohnsman8]

Re: Formula to match patial text string.

Is there a way to use this formula with Index to return a cell value and not just the row location of the match?

 

[RoryA]

Re: Formula to match patial text string.

Which formula are you referring to?

 

[bjohnsman8]

Re: Formula to match patial text string.

It is similar to this one:

=IF(COUNTIF(MBQR!G$1:T$3439,"*"&ACES!I2&"*")>=ROWS(1:1)),SMALL(IF(ISNUMBER(SEARCH(ACES!I2,MBQR!G$1:T$3439)),ROW(MBQR!G$1:T$3439),""),ROWS(1:1)),"")


I am trying to return a value based on that match in this row MBQR!R1:R3439.
Vlookup will not work as the values I am looking for are matching in text strings.

 

[RoryA]

Re: Formula to match patial text string.

Not sure what your last comment means as VLOOKUP works with text too, but it will only return the first match.

Anyway, you can use:

=IF(COUNTIF(MBQR!G$1:T$3439,"*"&ACES!I2&"*")>=ROWS(1:1),INDEX(MBQR!$R$1:$R$3439,SMALL(IF(ISNUMBER(SEARCH(ACES!I2,MBQR!G$1:T$3439)),ROW(MBQR!G$1:T$3439),""),ROWS(1:1))),"")

 

[wilsonlow]

Re: Formula to match patial text string.

This formula is helpful for me too, but I would like to look up the full strings against the partials, so the other way around...
How would this be made possible?

In the end I would like to have an abbreviation of the partial (in the column beside it) returned when the match between each string in a list of full strings is made with an array of summaries (partials) of all these full strings.

Example:
Equipment description list (table 1) (full strings)
hand operated valve to protect vacuum pumps
hand operated vent valve
hand operated valve to TRV
hand operated drain valve

Partial description list (typical; table 2) (partial)
drain
vent
hand operated valve

With next to it (in table 2) the three corresponding abbreviations (abbrvs)
DRN
VNT
HOV

In table 1 I want a cell returning the correct abbreviation next to the full string, having matched both via the partial, so:
hand operated valve to protect vacuum pumps | HOV
hand operated vent valve | VNT
hand operated valve to TRV | HOV
hand operated drain valve | DRN

If anyone could help me out on the combination of these two challenges I would me most grateful!

Any suggested solutions for this? I'm facing the same situation. thanks