-1

I have cells in a google sheet which have long abstracts. I need to mark/categorize these abstracts in an adjacent column, according to a table of phrases and corresponding tags in another sheet. As a newbie to google sheets, I'm unable to find the right combination of functions to do the job. So was wondering if someone could help me out? I have tried this to just search through the abstracts but its returning the error #VALUE! - =VLOOKUP('phrases and tags'!A$2:B$16(range of cells with phrases and their respective tag),H212(cell containing abstract),2,FALSE)

Thanks!

0

You can try to build a function with some if statements, and regexmatch :

Example : - Cell A1 is your abstract : "a beautiful cat in the street" - Cell B1 is your tag : "cat"

You can try this : =REGEXMATCH(A1,B1) It returns TRUE

To check multiple tag you can try then

=IF(REGEXMATCH(A1,B1)=TRUE,B1,
       IF(REGEXMATCH(A1,B2)=TRUE,B2,
           IF(...)
        )
 )

Keep in mind this is not an efficient solution because regexmatch is case-sensitive when you use it this way. Moreover if you need to test a lot of tags it's better to try a Query or GScript.

This is my understanding of the problem. You can try to share a spreadsheet with the desired result, it will be easier for us to understand it.

  • Hi, Here's a sample sheet. docs.google.com/spreadsheets/d/… Basically I want to search through the summaries for the phrases, and if a phrase is found, fill the column next to the summaries according to the table in the 'rules and tag' sheet. THanks! – Haryy Jul 31 at 16:39
  • Haha, i'm not sure i understood what you need but i gave it a try : docs.google.com/spreadsheets/d/… I add a column to clean the tags with TRIM and LOWER to minimize case-sensitive issues. I have done the same for the summaries but directly in the formula. – pjmg Aug 1 at 8:59
  • Thanks for the sample. It did work however it marks the article more than once even though its the same tag, if it finds the phrases for that tag more than once. Do I need to replace the & operator here with something else to make it into one long OR expression? Also, if I add more tags and phrases to my tags sheet then I would need to update the formula. Is there anyway so that the user only needs to add more phrases and corresponding tags to the tags sheets and not update the formula, without the use of a script? – Haryy Aug 3 at 12:54
  • Hey i updated the sample sheet. Is it ok now ? I used some Query to achieve this result. It's not fully automated tho, you need to expand the formula in the E column on the work-sheet tab. – pjmg Aug 3 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.