Good day all.

After some help please.

I have a spreadsheet and trying to find out whether certain words exist in a cell.

In simple terms, I would want to know if the cell contains either of the words ‘Tom’, ‘Dick’, ‘Harry’.

How could I do this?

Thanks!

Ctrl-F??

Try this.

Is this an ongoing or one off thing? One off, use ctrl f as above.

On going? Use a different software package suitable for what you’re doing or re-do your sheet so the data is in distinct cells rather than buried in text strings.

Depending on what you are trying to do, you can use conditional formatting to highlight cells that contain specific words. You can this multiple times with a different colour for different target words. You can then sort the data according to cell colour if you want to find all the cells that contain the target words. This is useful for some one-off jobs.

If the cells containing data are in cells a2 downwards and your “Tom” “Dick” and “Harry” words are in cells F2, F3 and F4 then

=If(Iferror(find($F$2,a2,1)+Iferror(find($F$3,a2,1)+Iferror(find($F$4,a2,1),0)>0,”Y”,”N”)

It’s not pretty or particularly clever but for a one off with a list of three words to look for it’ll work. If you’ve got lots of values to look for then something with arrays as per slowoldman’s link should work.

Off the top of my head, if the searched text is in A1 then in B1 type

=((instr(A1, “Tom”) + instr(A1, “Dick”)) > 0)

That should return true if Tom or Dick is found in the text.

Isn’t it vlookup?
76% of all the Excel help threads on STW are answered with a vlookup.

Think we might need a little more context? For example, it might be you want to count the amount of times a staff name exists so they have equal amount of students to act as a personal tutor for, in which case I go with a countif next to a list of staff names.

Tomorrow’s headline:

Man On Internet Asks For Help Looking For Dick