Extracting text from a random string and matching to a list

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
 
Upvote 0
Try:

Book1
ABCD
1
2Wrong versionThe module is the wrong version, refer to tech department
3Non Functional ModuleModule has failed, refer to tech department
4ExpiredLicense has expired
5Wrong colorIt's not pretty
6xxxxxxxxxxxx
7
140
141
142Model 407 | Error1457 | Non functional module | incorrect referral | category 1Module has failed, refer to tech department
Sheet27
Cell Formulas
RangeFormula
D142D142=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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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