Extracting text from a random string and matching to a list

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
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.
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Wow, that is amazing! Thank you very much!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,017
Messages
5,545,517
Members
410,689
Latest member
ConfuzzledThomas
Top