For reasons that aren't necessary to add here, I have an Excel 2007 spreadsheet that contains text in several cells. The text in these cells ranges from less than 100 words to almost 500 in some cases. Every once in a while, I need to find one word within some of these cells.
Previously, I had been just scanning the cell with my eye after using Excel's search feature to narrow down which cells contained the word. While doing this, I got to wondering if there might be a more efficient way to go about this. I also began wondering how much more tedious and prone to error this method might be if I were working with cells that contained even more text. Is there any easy way to conduct a search within a cell, in other words, without resorting to some complicated formula or the use of VBA?
Granted, I could simply copy and paste the text into a Word document and pinpoint the word through CTRL+F in Word, but is there an easy way to simply search for and identify a specific word within a cell in Excel? I've pasted a visual of this for you below:
In the cell you see above, I have highlighted the word "dispuestos" after scanning this cell with my eye. Is there a way to easily do the same using an Excel search feature? For example, in a Word table, I know that using the tab key can take me from cell to cell, but if I want to tab within a cell, I simply press CTRL+TAB. Is there some sort of similar technique I can use to search within an Excel cell? Is there something I need to alter in the options for Excel to make this happen?
Best Answer
I'm thinking the only way is VBA, so I've broken the rules to write a macro (...sorry) for you that can achieve this quickly. It will colour every occurrence of the search term orange and tell you how many occurrences it finds. You could create a shortcut to it like CTRL+SHIFT+F (like a special CTRL+F).
By the way, it works the same as CTRL+F as far as selections go. That is to say, if only one cell is selected then it searches the whole sheet... but if a range of cells is selected then it only checks that specific range, just like the regular Find function.
Here's an example result:
And here's the VBA:
It is case insensitive. To make it case sensitive, remove the four instances of
LCase()
.If you frequently search the same string then change the line
strSearch = ""
line to [for example]strSearch = "apples"
. You can still overwrite it when you launch it.