CODEWITHSUNDEEP
excel
user2885034
user2885034

Reputation: 21

Excel: search if a specific text exists in a column

I have two columns. Each cell in column A contains a full sentences and each cell in column B contains a word or phrase. I would like to check if the contents of each cell in column B appears in one of the cells in column A---it could appear in multiple cells in column A or in no cells. The output just needs to be a yes or no (and should be spit out in column C) for my purposes, but it would be neat to return the number of times each column B word came up somewhere in Column A.

So far I haven't figured out how to take a discrete string of letters (already printed in one cell) and search across a range in a column. Not sure if this is beyond the regular excel functionality.

Thanks very much for your help!

Upvotes: 1

Views: 72088

Answers (2)

Deb
Deb

Reputation: 131

Try This :

=countif(a:a,"*" & b2 & "*")>0  gives you result in True/Flase

To get the occurrence

=countif(a:a,"*" & b2 & "*")

To get YES/NO

=if(countif(a:a,"*" & b2 & "*")>0,"YES","NO")

Upvotes: 2

L42
L42

Reputation: 19737

Use array formula like this:

=SUM(IF(ISERROR(SEARCH(B1,A:A,1)),0,1))

enter in formula bar then press CTRL+SHIFT+ENTER.
Hope this helps. Put formula in C.

Upvotes: 3

Related Questions