Forum Discussion
Need to find multiple search terms within my spreadsheet
Hello,
I would like to write a code to find if any terms from my list (typed in column B) are found in column A.
| Column A |
Susan Prair |
Chalk Railroad |
Troublsom Waters |
Liszette Throm |
Electric and Light CO |
Christmas Town INC |
District of Maddville |
ETC |
Column B (List of search terms) |
USA |
Water |
Town |
City |
Company |
ETC |
In my completed spreadsheet I have about 50 search terms in column B that I need to check for and column A is about 6,000 rows! It would be helpful to highlight or write true/false next to each cell where one of my search terms appear. I don't need to know which search term appears just that one of them did appear...
Right now I’m doing it one-by-one and it is very time consuming. Thank you if you are able to help!!
This may work for you. SEARCH is not used because of the potential for false positives (e.g. "USA" would be found in "Susan", etc.).
=IF(SUM(IFERROR(FIND(terms,A2),0))<>0,"A term is present","")
8 Replies
This may work for you. SEARCH is not used because of the potential for false positives (e.g. "USA" would be found in "Susan", etc.).
=IF(SUM(IFERROR(FIND(terms,A2),0))<>0,"A term is present","")- Patrick2788 Yes!! I am amazed you understood what I meant (even picked up on my Susan/USA issue! This is exactly what I need.
So let's say Column A is the content, Column B will say "term is present/blank", and column C I will put my list of words.
The code in cell B2 will be: =IF(SUM(IFERROR(FIND(C:C,A2),0))<>0,"A term is present","") ?? Thank you so much!- Glad it works for you. You are welcome!
- OliverScheurich SergeiBaklan Hello, I saw you were able to solve a similar problem. Was wondering if you would be able to look at this. Thank you very much if you are able to solve this problem 🙂
- Using the Match function might work. But it will do it one after another. It has to be something that can be automated
- Better still use pivot table
