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

1. ## 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

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

May be this

Formula:
`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

3. ## 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

4. ## 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. ## 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:
`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.

6. ## 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. ## 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.

8. ## 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

9. ## 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

10. ## 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. ## 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. ## Re: How to search for specific words in text string and assign category based on these wor

Ciao,

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

13. ## 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. ## 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. ## 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.

16. ## 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.

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

#### 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