CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin
user5176291
user5176291

Reputation: 167

If string contains text from list, write matched text to cell

I'm working on a large Google spreadsheet with key phrases, strings of text, in column A. I want to search column A based on a list of keywords that live in another sheet. When a keyword matches a word in a string in column A, I want to print that word in an adjacent cell to column A.

Here's a simple spreadsheet to work with that I think demonstrates what I'm trying to do.

https://docs.google.com/spreadsheets/d/1tNcroABVP0UdP4CiJldxLZgdrJF33TYT4mL1DZJfD1Q/edit?usp=sharing

Upvotes: 1

Views: 5534

Answers (2)

player0
player0

Reputation: 1

I want to print that word in an adjacent cell to column A

=ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A, LOWER(TEXTJOIN("|", 1, 'KEYWORD LIST'!A2:A)))))

0

Upvotes: 2

user11982798
user11982798

Reputation: 1908

Try with this (put at B2):

=arrayformula(if(ISNUMBER(find(
        transpose({'KEYWORD LIST'!$A$2:$A$6}),A2:A6)), 
        transpose({'KEYWORD LIST'!$A$2:$A$6}),""))

Upvotes: 0

Related Questions