+ Reply to Thread
Results 1 to 16 of 16

How to search for specific words in text string and assign category based on these words

  1. #1
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    How to search for specific words in text string and assign category based on these words

    After struggling for some time I found a partial solution but it does not work all the time.
    In a nutshell, I have rows with text derived from Point of Sale program which contain descriptions of the restaurant orders.
    First, I need to derive specific words from the test string:
    Florence
    Wrp
    InL
    Ind
    In

    and then I need to break the orders into French and Indian choices based on those derived words. I have a corresponding categories to each derived word. So, if the word is "Florence" then the category should be French, if it is "In" then Indian.

    The problem with the LOOKUP formula i am using is that sometimes it returns the wrong words. Foe example, if the cells says "Florence entry in macadamia sauce" it will derive the word "in" instead of "Florence".
    thank you so much for anyone trying to help me
    Attached Files Attached Files

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: How to search for specific words in text string and assign category based on these wor

    May be this

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You could use find instead of search to mach case where you can eliminate error as you mentioned
    Click just below left if it helps, Booşathì

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,954

    Re: How to search for specific words in text string and assign category based on these wor

    =LOOKUP(1E+100,SEARCH(D$2:D$6,A2),F$2:F$6)
    Try this
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    Re: How to search for specific words in text string and assign category based on these wor

    Hey,Boopathy,
    too bad you are not in Denver or i would bring you an apple pie!
    It worked. However, there is a little problem.
    For example, there are some cells like "InL CkBx, Soda 20oz" and"Soda Can".
    If I would like the first example to be categorized as "Indian" and second as "Beverage" what do I do?
    thank you

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to search for specific words in text string and assign category based on these wor

    Find text.xlsxThis also works with your example

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For the second part of your question. It is a case of "First come first served". This goes both for the order of the items in column D and the order of the text in each cell of column A.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: How to search for specific words in text string and assign category based on these wor

    Try

    Edit Cell D6as "Soda" and F6 as "Beverage"

    [QUOTE=
    For example, there are some cells like "InL CkBx, Soda 20oz" and"Soda Can".
    If I would like the first example to be categorized as "Indian" and second as "Beverage" what do I do?
    [/QUOTE]

  7. #7
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    Re: How to search for specific words in text string and assign category based on these wor

    Newdoverman,
    thank you so much for your reply. Your formula, actually returns more correct results than other ones.
    However, I need your help in understanding why 2 different formulas (VLLOKUP and INDEX MATCH INDEX SEARCH) produce different results. For example:

    InL VgBx (Regular Price), AbL 3Falafel (Regular Price), InL CkBx (Regular Price)
    Index formula: Indian
    VLOOKUP: Arabic

    Please, find attached file to see the details.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: How to search for specific words in text string and assign category based on these wor

    Hi,

    an example:

    InL CkBx (Regular Price), Soda Can (Regular Price)


    contains three (edit) possible "match" to E2:E31: "InL", "Soda Can" and "Soda".

    using INDEX(...(MATCH(TRUE... you'll get the first one

    using VLOOKUP(LOOKUP(9999... the last one

    If you play with the order list , the "hierarchy" of E2:E31, as formulas read the list from E2 to E35, you could get a better alignment between two formulas outputs.

    Forgive my poor early morning English.

    Regards
    Last edited by canapone; 10-12-2014 at 01:22 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  9. #9
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: How to search for specific words in text string and assign category based on these wor

    Hi again,

    an example: you could write in E32 "InL" and G32 "Indian", after having set new ranges (E2:E32) read by formulas you will record new outputs from VLOOKUP(LOOKUP.

    Hope it's a little help.

    Regards
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    Re: How to search for specific words in text string and assign category based on these wor

    thank you so much, canapone, or should i say grazie!
    So, when i use LOOKUP the formula starts searching from right/last and then INDEX from first/left.

  11. #11
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    Re: How to search for specific words in text string and assign category based on these wor

    But the order in which the search words are grouped in the columns is also very important.
    So, if i have Ind>Indian earlier than Ab/Arabic then the formula will return the first one it finds.
    well.....
    it makes it complicated how to
    1) construct the table with search words and
    2) which order left (INDEX MATCH) or right (VLOOKUP) to use

  12. #12
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: How to search for specific words in text string and assign category based on these wor

    Ciao,

    thanks for your kind feedback.




    A little experiment to study

    =VLOOKUP(LOOKUP(E1+100,FIND(E$2:E$32,A2),E$2:D$32),$E$2:$G$32,3,0)


    go the formula bar, select only the segment

    FIND(D$2:D$6,A2)

    and press F9

    you'll see a series of #VALUE! errors that LOOKUP ignores. LOOKUP is searching for a big number -E1+100 (you could substitute with LEN(A2)+1) and stops its hopeless search at the last number/position produced by FIND (...).

    More or less.

    Saluti da Firenze
    Last edited by canapone; 10-12-2014 at 02:29 AM.

  13. #13
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    Re: How to search for specific words in text string and assign category based on these wor

    One more quick question:
    if the cell contains "Btld Wtr (Regular), InL CkBx (Regular Price)"
    The INDEX MATCH formula returns Beverage
    and LOOKUP returns Indian

    I have put "Wtr" before "In" in the reference table.
    So, I understand the result of the INDEX MATCH formula, it searches for Wtr first and finds it first.
    However, i do not understand why LOOKUP returned Indian. Does it ignore the fact that Wtr is positioned before In in the table?

  14. #14
    Registered User
    Join Date
    10-05-2014
    Location
    denver
    MS-Off Ver
    8
    Posts
    12

    Re: How to search for specific words in text string and assign category based on these wor

    Canapone, i appreciate your help. I need to think this over and might have few more questions after I digest all the information. Do you mind having a look at this thread tomorrow or in the next few days? Maybe you can answer some questions i might have.

  15. #15
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: How to search for specific words in text string and assign category based on these wor

    Ciao,

    you're welcome.

    In the attached example there is a new list (old list but different hierarchy).

    The string "Btld Wtr (Regular), InL CkBx (Regular Price)" contains 3 substrings of the list: Wtr, In and InL.

    Using F9 over LOOKUP you will see:

    LOOKUP(1E+100,{6.#VALUE!.#VALUE!.#VALUE!.21.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.21.#VALUE!}

    "Wtr" has been spot after 5 characters (Btld"space") in position 6 , In and InL after 20 characters in position 21 ("Btld Wtr (Regular), ).

    In the example Inl is the second 21 produced by LOOKUP, the last number: so LOOKUP(E1+100... returns the position of last number occurred: in the example InL/Indian.

    I hope not to be wrong.
    Attached Files Attached Files
    Last edited by canapone; 10-12-2014 at 06:36 AM.

  16. #16
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to search for specific words in text string and assign category based on these wor

    In lookup formulae, the order of lookup means everything. The order of the lookup list is counter-intuitive in that if you have values like IN and INK you will want to have INK before IN because the formula (normally) will find IN and stop if it finds that and INK will not be found if it is after IN.

    INDEX lists the values that you are wanting to retrieve followed by a row reference and a column reference. The row and column references can be determined by a variety of means and the order of the columns isn't important. This is followed by the type of return you want...exact or not

    VLOOKUP requires the value to be looked up then the ARRAY and the column followed by the type of return...exact or not.

    Regardless of the method that you prefer and your data permits, the order of the list holding the compare items can greatly simplify the formula required to retrieve the correct value. This is likely the only part of the data that you have control over as the order of the values to be found in "free" text can be any order at all.

    The article at this location http://www.randomwok.com/excel/how-to-use-index-match/ gives pretty good explanations and comparisons of VLOOKUP and INDEX MATCH for looking up data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search string of words in text
    By excelnik in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-31-2013, 05:10 PM
  2. [SOLVED] Search Cells for key words and add category code
    By Jimbo42 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-07-2012, 05:23 PM
  3. Locate a reference in a table based on specific words on a text
    By zetavares83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-06-2012, 06:17 PM
  4. Replies: 1
    Last Post: 04-04-2012, 07:15 PM
  5. How do I do a multiple search using key words in a text string
    By patricia tipp in forum Excel General
    Replies: 1
    Last Post: 02-28-2006, 01:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1