CODEWITHSUNDEEP
excelexcel-formula
Will
Will

Reputation: 1

Formula to find presence of certain keywords in a cell

I need to find certain keywords such as blogger or writing or writes or .com in each cell belonging to a column (it's a bio). Specifically a formula to pick up any words that would indicate that the cell being referenced is a person who has a blog.

I was thinking of nesting IF and OR statements, but haven't been able to find anything yet.

Total Excel noob here. Would appreciate any help you guys can provide!

Here's an example of the data I'm dealing with:

SO19687510 question example

Upvotes: 0

Views: 54780

Answers (3)

Sam
Sam

Reputation: 7303

The FIND function by itself won't work in this instance as it will return #VALUE error if the text isn't there,

You can use the ISERROR function to help deal with this problem.

=IF(ISERROR(FIND("a",A21)),FALSE,TRUE)

If you want to check multiple conditions then you could use the AND function as below:

=IF(AND(ISERROR(FIND("a",A21)),ISERROR(FIND("b",A21)),ISERROR(FIND("c",A21))),FALSE,TRUE)

NB. This is checking Cell A21 in this instance.

If you want to check an entire column, using COUNTIF is a good suggestion, you could also nest these in an OR function to check for multiple instances.

=IF(OR(COUNTIF(D:D,"="&"*foo*")>0,COUNTIF(D:D,"="&"*bar*")>0),TRUE,FALSE)

Upvotes: 1

pnuts
pnuts

Reputation: 59450

With your list of keywords in ColumnD starting in D1, then in Row1: 

=COUNTIF(A:A,"="&"*"&D1&"*")

copied down to suit should give you the number of instances of each keyword found in ColumnA. Summing the results may be a good guide.

Upvotes: 1

user2687130
user2687130

Reputation:

A1 would be the search cell, and in this cell TRUE would be present if A1 contained the "SEARCH TEXT" and it would display "FALSE" otherwise.

=if(FIND("SEARCH TEXT",A1),"TRUE","FALSE")

Source: http://forums.tutorialized.com/ms-excel-97/in-excel-how-to-search-a-text-string-get-true-5215.html

Upvotes: 1

Related Questions