Finding Repeated Words

[Té]

Is there a way to find repeated words in a spreadsheet, as well as how many
times the word is repeated besides using the COUNTA function? I was thinking
of filtering, and then applying the COUNTA function. Is that the easiest way?

 

[T. Valko]

If you use a filter then you want to use the SUBTOTAL function. SUBTOTAL
will only calculate the visible rows.

=SUBTOTAL(3,A2:A100)

 

[Shane Devenshire]

Hi,

Is this a general question or are you looking for specific word that might
be repeated? Are these words located in any particular location, a column,
row, or worksheet? And by words do you mean text entries only not numbers?

Suppose the range was A1:C14 and we are checking for any non-blank cells
which repeat then the following formula would do it:

=SUMPRODUCT(--(COUNTIF(A1:C14,A1:C14)>1))

This formula would count three occurances of the work Dog as 3 repeat words,
which may or may not be what you want.

 

[Rick Rothstein]

Another possibility for you to consider. First, right click the Status Bar
at the bottom of the Excel window and select Count from the list that pops
up. Next, select the range of cells that you want to search, then click
Edit/Find from Excel's Menu Bar, type the word you want to find in the "Find
what" field, click the Options>> button to show the full options available
(unless they are already displayed) and select whatever options you want to
apply (I'm thinking mainly of the "Match entire cell contents" option here),
then click the Find All button and press Ctrl+A, and, finally, click the
Close button. All cells with your word in them will be selected and the
count for these selected cells will be shown in the Status Bar.

 

[Té]

Thank you sooooo much!

Hi,

Is this a general question or are you looking for specific word that might
be repeated? Are these words located in any particular location, a column,
row, or worksheet? And by words do you mean text entries only not numbers?

Suppose the range was A1:C14 and we are checking for any non-blank cells
which repeat then the following formula would do it:

=SUMPRODUCT(--(COUNTIF(A1:C14,A1:C14)>1))

This formula would count three occurances of the work Dog as 3 repeat words,
which may or may not be what you want.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire