I have some cells with multiple lines of text, and I'm looking for a specific word in that text. So for the example below, every row that contains the word "Canada" should return as "TRUE"
| A | B | C |
|---|---|---|
| Jeffrey | Canada, Male | Hi, I'm from Canada! I love to play hockey |
| Priyanka | UK, Female | I grew up in London |
| Victoria | USA, Female | I'm originally from Canada but now live in NYC |
I guess I'm thinking about this the wrong way, because IF SEARCH seems to only return an exact match for the selected cell(s), which of course is an issue for cells with unique text. I'm really not sure what else to try.
CodePudding user response：
In your situation, how about using REGEXMATCH as follows?
Sample formula:
=ARRAYFORMULA(IF(REGEXMATCH(A1:A&B1:B&C1:C,"Canada"),TRUE,""))
- In this sample formula, the values of columns "A", "B" and "C" are joined and
Canadais searched usingREGEXMATCH.
Result:
When this sample formula is used, the following result is obtained.
Reference:
CodePudding user response：
use:
=INDEX(REGEXMATCH(A1:A&B1:B&C1:C; "Canada"))
be aware that regex is case sensitive so if you need so use:
=INDEX(REGEXMATCH(LOWER(A1:A&B1:B&C1:C); "canada"))