Forum menu
[Closed] Excel Guru's. Find certain words in cells?
I got help on here before, but cannot remember...
On a spreadsheet, I have cells which contain descriptions. I would like to identify whether, for example, cell A1 contains the words "Subscription", "Licence", "Software" amongst a string of other words.
How best to do this using a specific formula folks?
Thanks in advance.
Conditional Formatting is what you're after.
This might do what you want...
Select "Conditional Formatting" from the ribbon at the top then "Highlight Cells Rules" then "Text that contains"
Highlight Cells Containing Specific Text with Conditional Formatting (skyneel.com)
or if trying to do it with a formula:
=find("Software",A1)
if it contains Software it will return the position where it starts (so 'Software License' returns 1 and 'Free Software' returns 6). If its not there it returns a #VALUE error, so you can combine with iferror:
=iferror(find("Software,A1),"Not S/ware")
Or to make the output from polys suggestion a bit more readable
=If(iserror(find ("text", A1), "No", "Yes")
Note that FIND is case sensitive. If you want it not case sensitive then use the SEARCH function.
=IF(ISERROR(SEARCH("software",A1)),"No","Yes")
If you want to look for multiple targets (and disappear down the rabbit hole) then:
=IF(SUMPRODUCT(- -ISNUMBER(SEARCH({"software","licence","subscription"},A1)))=0,"No","Yes")
[Note: the two characters before ISNUMBER are two minus symbols. This converts TRUE and FALSE values into 1s and 0s]
If none of the solutions above work for you, then please PM me and I can write some code to do the job.