How to categorise based on a specific word in a cell?

Question

Hi guys,&nbsp;I am not sure excel can do what I need but here we go:&nbsp;In cell "A" I am being provided with a category with potentially lots of words. Based on one of those words I would like to categorise cell "B". Example below:&nbsp;Cell "A" phrase is: "NBA teams from New York and Boston"&nbsp;Based on the word "Boston" I would like cell "B" to automaticially be categorised as "Boston".&nbsp;How do I do that?&nbsp;&nbsp;I can make a XLOOKUP formel where I tell Excel that Boston = Boston, but what I need is for Excel to find out by itself that if cell "A" has the word "Boston" in it (no matter where it is placed), then cell "B" should automaticially be categorised as "Boston".&nbsp;How do I do this? I really appreciate any kind of help.&nbsp;Best regards,Henrik

subodh_tiwari_sktneer · Answer

A sample file would be helpful to visualize what exactly you are trying to achieve.

dmacgme · Answer

Your exact example could be solved using the FIND function which will look in one cell for text specified in another cell. If (*note IF) it returns a number, you know your string is present, then use IF(A1&gt;0,"boston","") and you get your desired word. However, as the previous response indicated, it is nearly impossible to give a more robust or complete answer to this question without more information.

henne79 · Answer

dmacgme&nbsp;&amp; Subodh_Tiwari_sktneer&nbsp;thank you so much for replying.&nbsp;I have attached an excelark to this message which should explain my problem.&nbsp;The "problem" is that I cannot edit the input (Colonne B) because it is given to me by a third party. Hope it make sense and that you can help me out.&nbsp;&nbsp;Best regards,Henne

subodh_tiwari_sktneer · Answer

Henne79&nbsp;&nbsp;Do you want formula in column F in the second table? If so, please try this...&nbsp;In F3&nbsp;=IF([@[If input contains…]]="","",IF(COUNTIF(Tabel1[Input (given to me by third party)],"*"&amp;[@[If input contains…]]&amp;"*"),[@[If input contains…]],""))&nbsp;and then copy it down.&nbsp;If this is not what you are trying to achieve, please rephrase your requirement based on sample file.

dmacgme · Answer

From what I see you can still accomplish this using the simple construction I mentioned earlier. Use the FIND function to look for a word match in the string of text that you are given to that which you have defined if there is a match print that word into the desired cell. You dont need to edit any data for this, it is a logical test.However, it is not clear if you are referring to your 'definition' table as a set or as individual cases, the way you have it laid out on the example makes it look like each line has its own definition table special case. I think you probably want to use the definition table as a complete set for comparison to make your matches rather than line by line.As in:If input data contains (Boston put Boston OR New York put New York..... and so on)Like you want to extract the city name from whatever text is given. If so this is not much more complicated, but you will need to expand the function to include all of your city names not just one at a time. Include the set of names and implement the same logic.

Forum Discussion

How to categorise based on a specific word in a cell?

5 Replies

Resources