Extracting text from a random string and matching to a list

[HughT]

I have a csv file which I have downloaded to Excel. The name of this worksheet is 'Tickets'. This contains text strings of random length which include standard 'labels' of random length. The text is in column C. The phrases in the text are separated by "|" which may assist. I have a separate table in a worksheet called 'Labels' of the definitions of the label text in column A and their meaning in column B.

What I want to do is match the label text in the column A of the Label worksheet with the same text included in the randomised text in column C of the Tickets worksheet, and return the label definition in column B of of the Labels worksheet in column D of the Tickets worksheet.

So in principle, match definition in Labels column A with text in Tickets column C and return the value in Labels column B in Tickets column D.

I have tried using Vlookup with Range Lookup 'True' but this produces too many errors because the Labels include similar text, and Text to Columns separates the text into multiple columns cleanly but I then have the problem of finding the text across a range of cells for each definition.

Example of Ticket text (column C of 'Tickets'):

Model 407 | Error1457 | Model 407 | Non functional module | incorrect referral

"Non functional module" (the text to search for) can be placed anywhere in this text string.

Example of Label text

Column A Column B
Non functional module Module has failed, refer to tech department

"Module has failed, refer to tech department" is the text to return in column D of the Tickets worksheet.

Many thanks

HT

 

[DanteAmor]

Try this array formula

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:23.76px;" /><col style="width:22.81px;" /><col style="width:372.59px;" /><col style="width:159.68px;" /><col style="width:40.87px;" /><col style="width:127.37px;" /><col style="width:153.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td >C</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:37px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >Model 407 | Error1457 | Model 407 | Non functional module | incorrect referral</td><td >Module has failed, refer to tech department</td><td > </td><td >Non functional module</td><td >Module has failed, refer to tech department</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >Model 407 | Error1457 | Model 407 | Non functional machine | incorrect referral</td><td >Machine has failed, refer to tech department</td><td > </td><td >Non functional equipment</td><td >Equipment has failed, refer to tech department</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td >Model 407 | Error1457 | Model 407 | Non functional equipment | incorrect referral</td><td >Equipment has failed, refer to tech department</td><td > </td><td >Non functional machine</td><td >Machine has failed, refer to tech department</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formelu</td></tr><tr><td >D2</td><td >{=INDEX($I$2:$I$4,MAX(IF(--ISNUMBER(SEARCH($H$2:$H$4,C2)),ROW($I$2:$I$4)))-1)}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

Note:
Change the ranges of columns H and I for columns A and B of your label sheet

 

[HughT]

Apologies for not responding sooner, I had to cover for a colleague and then went sick myself so the project was deferred.
I tried the above but couldn't get it to work, probably because I didn't explain it very well. To make more sense of it I have put the data onto a single worksheet:

In column A there is a long range of random text strings. In column B, 10 short text strings which are randomly included in the text in column A, and are to be used as search references for the text in column A. In column C a 'label' which is an instruction for what to do if a text string in column B is found within the text string in column A. Column D contains the formula required which returns the instruction label from column C matching the text in column B which has been found in the text string in column A.

So for example:

A142 text: Model 407 | Error1457 | Non functional module | incorrect referral | category 1
B3 text: Non Functional module
C3 text: Module has failed, refer to tech department
D142 text: Module has failed, refer to tech department

So the formula searches all of column A for all the text strings in column B, if it finds one it returns the matching text label from column C in column D in the same row as the searched for text was found in column A.

Many thanks


HT

 

[Eric W]

Try:

Book1
	A	B	C	D
1
2		Wrong version	The module is the wrong version, refer to tech department
3		Non Functional Module	Module has failed, refer to tech department
4		Expired	License has expired
5		Wrong color	It's not pretty
6		xxxxxx	xxxxxx
7
140
141
142	Model 407 | Error1457 | Non functional module | incorrect referral | category 1			Module has failed, refer to tech department
Sheet27

Cell Formulas
Range	Formula
D142	D142	=LOOKUP(2,1/SEARCH(B2:B6,A142),C2:C6)

If you want to leave some empty rows in your table for future use, fill them with something that won't match (like the x's in B6) or else it will return erroneous results.

 

[HughT]

Wow, that is amazing! Thank you very much!

 

[Eric W]

Happy to help! ?