Viewing 10 posts - 1 through 10 (of 10 total)
  • Excel guru’s. How to find specific words in a cell?
  • benz
    Free Member

    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!

    fazzini
    Full Member

    Ctrl-F??

    slowoldman
    Full Member

    Try this.

    dangeourbrain
    Full Member

    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.

    thols2
    Free Member

    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.

    bails
    Full Member

    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.

    thepurist
    Full Member

    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.

    thegeneralist
    Full Member

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

    GolfChick
    Free Member

    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.

    Cougar
    Full Member

    Tomorrow’s headline:

    Man On Internet Asks For Help Looking For Dick

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.

Thanks for popping by - why not stay a while?IT'S FREE

Sign up as a Singletrack Member and you can leave comments on stories, use the classified ads, and post in our forums, do quizzes and more.

Join us, join in, it’s free, and fun.