Searching Text inside Excel

One of the options in the Find dialog is "Match entire cell contents". Select it.

There is no option to do this natively in Excel, but there are a few possible workarounds depending on your needs.  The following links discusses several of them.

https://superuser.com/questions/479161/find-match-whole-word-only-workaround

There is no option to do this natively in Excel

Martin, if I'm reading this correct,  I think they want a word search, so the do want a match on "red plasma", which the "Match entire cell contents" won't match.

---

»bp

There is no way with the Search dialog to find cells that contain the matching "word" unfortunately.  Assuming you want things like "red door" and "was red, and blue" to be matched, but note "redo".

You could create some VBA code to do it, but that would take a little bit of work...

---

»bp

This workbook does that and I also made the search case-insensitive.
29140894.xlsm

Martin, that still seems to require a full match on the cell contents, rather than just a word match.

@oaktrees, can you confirm exactly what you want?

---

»bp

You tried with a space after the term required.

I have just tried with the following four phrases:

1. will have to redo
2. coated with red plasma
3. this is  not credible
4. red bucket

and searched for "red " ie red with space after. It went to the cells containing item 2 and item 4, so don't understand the issue.

IF  they are really looking for a true "word search" then the word to be found could appear at the beginning, end, or middle of a phrase.  So searching for a leading or trailing space only finds some of those.  And I suspect things like a period, comma, dash etc might also be valid word delimiters in addition to a space, making it a little more challenging.

Just the thing that RegEx is suited for, but I'm not proposing anything until oaktrees provides more clarity on the search criteria, and the desired results (a list of matches, highlighted cells, etc).

---

»bp

Hi Bill,

You wrote:


I believe that's it.  Something that can find when the word red is operating alone, and when it may have a comma after it, or a period, or otherwise.

Many thanks,

OT

I think this does what you want. Search for red.
29140894a.xlsm

Did you review the link I provided, there are multiple solutions to your issue: add-in, VBA function, ... all there for the taking.

Here is a slightly different approach that uses a regular expression as the search term.  Try running it in the attached example sheet with the following search term:

\bred\b

The \b is the regular expression tag for word boundaries, so will only find cells containing red as a "word".

It highlights all match cells on the current sheet in yellow and shows a count of matches at the end.

EE29140894.xlsm

---

»bp

Hi BP,

Got null results.

What do you think happened?

Thanks!

OT
null-results.jpg

Hi Daniel,

Did see those.  They look GREAT!  Checking now.

Thanks!

OT

You are using the default Search built in to Excel.  The solutions posted in this question by Martin and I involve running a custom Macro that we built, which will prompt for the search string, and then do the work.

Click the Developer tab, Macros, and then Run the "FindValue" macro.

---

»bp

Oh, WOW!

I mean... WOW!

How cool is THAT???!!!

Wonder if we can add finding them one by one?   The highlighting is USEFUL!!!!  Challenge is, with amount of data, and columns, it would be easier to jump from one to the next.

Possible?

Thanks,

OT

Try this. Click the button and search for red.
29140894b.xlsm

The above file was updated.

Here's yet another version which displays the Data and Search sheets side by side at the end of the search to make viewing easier.
29140894c.xlsm

Hi BP & Martin,

These are 1,100 quotes from a SUPER LONG book.  The goal is to review theme comments that were added to the quotes to discern a pattern, or even applicability, since research was done over time and initial themes may have collapsed under new data.  

As the book was read salient points were extracted as quotes, and potential themes were proposed.  So that the reader may have added the terms:

chromatography
isotope
carcinogen

next to quotes.  Now that the work has been read from beginning to end, need to go through each row on the basis of the theme terms, see the original quote, the proposed term, and reevaluate the strength of the relationship between the quote and the term.

So, searching for

carcinogen

would jump one from point to point.  You could see the quote (which may itself even contain the term) with the comment next to it.  

This will let the researcher consider if what struck him as possible evidence for a carcinogen still holds up, or if best to leave that quote out when presenting his conclusions.

Sincerely,

OT

I believe we've answered your original question so please close this question and add a new one for the above requirement.

Thank you, Experts! :)))))))))))))))))))))))

This should be a lot faster. I also did two other things:

• When the workbook is opened, the previous search results are cleared since you can't step through them anyhow.
• Added messages when the stepping through reaches the start or the end.

29141284c.xlsm

I just made a quick change.