• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assigning categories to different keywords from a text

LOGi

New Member
Hi!

I'm trying to assign different categories to keywords from a text. Texts I want categorized can be e.g. "lunch boston james" or "field expenses anderson". From here I want to find the different names in the sentence (both first and last names), and each name has different categories or divided into different groups.

E.g. "lunch boston james " category = marketing, where I have another sheet with E.g.
Names category
James hamilton marketing

I have tried this formula:

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

However, it only seem to work with one name and not both names. I want the formula to look for both names in each text because sometimes first names are written and sometimes the last names.

Can anyone help me with this?

Thanks in advance.
 

r1c1

Administrator
Staff member
@LOGi Welcome to Chandoo.org forums and thanks for posting your question.

Very interesting indeed. For better data management purposes, I recommend storing both names in 2 columns. But assuming the name is one cell and always in first name space last name format below solution should work.

Code:
=INDEX(cats,MATCH(1, 
IFERROR(SEARCH(LEFT(names,FIND(" ",names)-1),A3)>0,FALSE) + IFERROR(SEARCH(MID(names,FIND(" ",names),99),A3)>0,FALSE),0))
the named range cats refers to categories and names refers to the names.
 
Top