Results 1 to 2 of 2

Thread: Extracting text from a random string and matching to a list
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2012
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extracting text from a random string and matching to a list

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,851
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Extracting text from a random string and matching to a list

    Try this array formula

     ABCDEHI
    1  C    
    2  Model 407 | Error1457 | Model 407 | Non functional module | incorrect referralModule has failed, refer to tech department Non functional moduleModule has failed, refer to tech department
    3  Model 407 | Error1457 | Model 407 | Non functional machine | incorrect referralMachine has failed, refer to tech department Non functional equipmentEquipment has failed, refer to tech department
    4  Model 407 | Error1457 | Model 407 | Non functional equipment | incorrect referralEquipment has failed, refer to tech department Non functional machineMachine has failed, refer to tech department

    CellArray Formelu
    D2{=INDEX($I$2:$I$4,MAX(IF(--ISNUMBER(SEARCH($H$2:$H$4,C2)),ROW($I$2:$I$4)))-1)}


    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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •