is it possible to locate pattern in two different columns, where pattern is located randomly in text strings?

[blbat]

Is it possible to locate pattern in text strings in two different columns, return value of third column, where pattern may be in any random location within the text strings, and pattern to match varies from 6 to 10 characters?

So below cell A2 matches cells B2 and B3 (pattern = ZBQ-125) and returns C2 (Fred).
Cell A4 matches B4 and returns C4 (Paul)
Cell A5 matches B7 and returns C7 (Les)
I've tried the formulas in column D, shown below as well...no joy.
any help would be appreciated.

Excel 2007

	A	B	C	D
1	component:	alternate:	Owner:	result:
2	AD/ZBq-125(V)4, Random Access SET	AD/ZBQ-125 SET Random Access	Fred	NOT FOUND
3	ZL/LDC-144(V)4, Alpha	ZBQ-125	Fred	NOT FOUND
4	LZ-1278	AD/LZ-1278	Paul	NOT FOUND
5	some1234-xr	AD/ZBQ-125 SET Random Access	Fred
6	XR/ABC-445	AD/LZ-1278	Joe
7	no entry	1234-xr	Les

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula D2 =IFERROR(LOOKUP(9.99E+307,MATCH($B2,$A$2:$A$100,C2),$A$2:$A$60),"NOT FOUND") D3 =IFERROR(LOOKUP(9.99E+307,FIND($B3,$A$2:$A$100,C3),$A$2:$A$60),"NOT FOUND") D4 =IFERROR(LOOKUP(9.99E+307,SEARCH($B4,$A$2:$A$100,C4),$A$2:$A$60),"NOT FOUND") <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[Rick Rothstein]

Is it possible to locate pattern in text strings in two different columns, return value of third column, where pattern may be in any random location within the text strings, and pattern to match varies from 6 to 10 characters?

So below cell A2 matches cells B2 and B3 (pattern = ZBQ-125) and returns C2 (Fred).
Cell A4 matches B4 and returns C4 (Paul)
Cell A5 matches B7 and returns C7 (Les)
I've tried the formulas in column D, shown below as well...no joy.
any help would be appreciated.

I don't understand your "matching" rules. The first one involves three cells (highlighted in red) whereas the rest only involve two cells (see example highlighted in green)... can you clarify this for us?

 

[blbat]

thanks for your interest Rick-

and apologies, the obfuscation was unintentional!

in the example above, cell A2 value contains the text pattern " ZBq-125 " embedded in the entire text string.
this text pattern also appears in Cells B2 and B3. (Case insensitive)

Cell A5 contains the string 1234-xr. (within Cell A5 text string) Cell B7 also contains the string 1234-xr

Column A and Column B actually have values up to row 100 and beyond.

So what I am trying to do is almost like a Vlookup, only on an embedded pattern within the text strings.

man, that is really painful to try and put into English!!

 

[blbat]

Does the follow-on explanation make sense?

 

[Rick Rothstein]

Does the follow-on explanation make sense?

No, not really. Here is my problem... I do not see how you know what the code is that should be looked for on each row. For example, how do you know that ZBQ-125 is the code that should be matched on Row 2? Yes, I see it in cell B3, but how did you know to look there (you do not look to other cells for the other rows)? And on those other rows... how did you know to pick the shortest text as the code (that would not have worked for Row 2)? So, my problem is... how do you know what the codes are... what tells you which text in the cells are codes?

 

[blbat]

First, thanks for your time Rick..I truly appreciate it.

How did I know which code should be matched from column to column?
The answer is, my eye is trained to look for those code patterns...so I'm literally looking at the first cell in Column A, spot the pattern within the text string in the cell, then visually go look for a match in column B.
(Think of that code as a Model number for a component- the model number is always the same.)
what makes it difficult to automate is the fact that, once you spot the pattern, it can appear in column B in any random placement.

What I mean by that is that the pattern- a text string- remains constant from column to column, but can be embedded anywhere in the ENTIRE text string. that means I can't use a simple LEFT or RIGHT to pull it out.

I'm not sure this is possible to do with a simple formula- I might have to resort to VBA to get it done.
(write a component definition list, then create a loop to check for the definitions within my target columns.)

 

[Rick Rothstein]

First, thanks for your time Rick..I truly appreciate it.

How did I know which code should be matched from column to column?
The answer is, my eye is trained to look for those code patterns...so I'm literally looking at the first cell in Column A, spot the pattern within the text string in the cell, then visually go look for a match in column B.
(Think of that code as a Model number for a component- the model number is always the same.)
what makes it difficult to automate is the fact that, once you spot the pattern, it can appear in column B in any random placement.

What I mean by that is that the pattern- a text string- remains constant from column to column, but can be embedded anywhere in the ENTIRE text string. that means I can't use a simple LEFT or RIGHT to pull it out.

I'm not sure this is possible to do with a simple formula- I might have to resort to VBA to get it done.
(write a component definition list, then create a loop to check for the definitions within my target columns.)

There is no way to program Excel (either with a formula or VBA) to find a code number that you recognize as being a code number by sight. If your code numbers had a fixed pattern to them, then something might be possible, but from what you posted so far, I do not see such a fixed pattern. The only approach I can think of is for you to list the possible codes and then create a macro or UDF (user defined function), both of which are VB coded solutions, to process the list.

 

[blbat]

They DO have a fixed pattern...there will always be at least six, sequencial, characters within the text string that will match from Column A to Column B.

so for two text strings in two different cells:

Cell A2:
AD/ZBq-125(V)4, Random Access SET

Cell B4:
AD/ZBQ-125 SET Random Access

the characters that appear in sequence in both cells are: ZBQ-125

So, I would need to parse the cells in column A starting at A2, then compare that to each cell in Column B, looking for six characters that appear in the same sequence in a cell from each column.

I just made my own brain hurt!

 

[Rick Rothstein]

They DO have a fixed pattern...there will always be at least six, sequencial, characters within the text string that will match from Column A to Column B.

so for two text strings in two different cells:

Cell A2:
AD/ZBq-125(V)4, Random Access SET

Cell B4:
AD/ZBQ-125 SET Random Access

the characters that appear in sequence in both cells are: ZBQ-125

Can you expand on the words "at least" that I highlighted in green. If there could be more (which is what the words suggest), then how will I know what is the code and what is not the code? For example, the ZBQ-125 you posted as being the code is 7 consecutive characters BUT in the values you give above AD/ZBQ is six consecutive characters that are identical between the two cells... how is the program to know that is not the code you are after? For that matter, if the "at least" indicates there could be more than 6 characters, how is the program to know that AD/ZBQ-125 is not the code?

 

[blbat]

apologies if I am pulling you down a rabbit-hole Rick.

yes, to expound on the "at least" statement I made-

by "at least" I mean the minimum number of characters in the text pattern will be at least six characters, but can be as many as ten characters. (as long as the characters are sequencial.)

You are correct, the text string: AD/ZBQ-125 would indeed meet my criteria, as it appears in both columns.