Find all instances

R

Richard

I have a large but simple Excel spreadsheet (Excel 2000)--no formulas, just
text. Someone told me that there's a way of searching for all occurences of a
word in the sheet. That is I'd go to Edit, Find, type in "John" and get all
the occurences of "John" with a total number (53 "John" in the sheet).

Is this possible? I can't figure it out. Or is it possible only in later
versions of Excel.
 
A

Alan Beban

Richard said:
I have a large but simple Excel spreadsheet (Excel 2000)--no formulas, just
text. Someone told me that there's a way of searching for all occurences of a
word in the sheet. That is I'd go to Edit, Find, type in "John" and get all
the occurences of "John" with a total number (53 "John" in the sheet).

Is this possible? I can't figure it out. Or is it possible only in later
versions of Excel.
Click to expand...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayMatch("John", relevant_range), array entered into a two-column
range at least large enough to accommodate the number of occurrences,
will return an array of the row and column numbers within the range.
Third and fourth optional arguments allow the output to be the cell
addresses of the occurrences; and a fifth can be used if case sensitive
matching is desired.

Alan Beban
 
Top