I have an Excel workbook in which each cell in column B contains a short fable (story). These cells (which are formatted "General") contain text values that are large by spreadsheet standards: all but one is over the 255 character limit for text display, one cell contains more than 3,000 characters, and I guess that most are between 500 and 700 characters. The second illustration below shows such a cell.
In another workbook, I have (meta)data about the fables. In particular, I am interested in frequency of occurrence of some words. In the illustration immediately below, Column C contains a list of the words I am interested in. Column B contains the total number of occurrences of the corresponding word (counting multiple occurrences within a fable); it is irrelevant to this question. I also want to count the number of fables in which each word appears (at least once). I call this "Dispersion" (see explanation in comment below). I have been using Ctrl+F ("Find and Replace") and clicking "Find All", which reports a count of the cells that contain the search string (see second illustration below). I did this one at a time, for each word, and manually entered the numbers into Column N.
(You can probably skip this paragraph.)
I need counts for single, whole word instances, and not derived forms of root words (not even plurals). For example, my count for "animal" needs to return counts for just "animal," not "animales" or any other such variation.
Early on, I realized that simply searching for a word
could lead to a false count
because it would include words that contained the word I was searching for.
I got around this by padding my search terms with spaces at the beginning and end – in Column E (e.g., " animal "), which contains
=" "&C2&" " –
and ensuring that the column against which I am checking these words also isolated such words.
Wherever a punctuation mark fell adjacent to the last or first letter in a word in a fable, I inserted a space to eliminate any such adjacency.
For example, "todo esto, porque siendo" became "todo esto , porque siendo".
(This was partially inspired by JNevill's comment on this question:
Excel's COUNTIF doesn't work.)
My Ctrl+F searches then returned for me counts for single instances of the words I searched for.
Of course this is tedious, time consuming and error-prone, so I got to wondering if some formula wouldn't be able to do the same thing, but faster.
A couple of posts/web pages suggested that the
COUNTIF function might be able to accomplish this, so I've been experimenting with it, but thus far, my attempts have failed every time.
In the above illustration, Column M contains
and as you can see, it is displaying a "0" when it should be matching the number in Column N (i.e., the count found using Ctrl+F).
(As stated above, Column E contains the string I am looking for,
which is the word I am looking for,
padded with spaces at the beginning and end.)
Every configuration using the
COUNTIF function I have tried has failed.
Below is an image of cell B23 in my fables worksheet (i.e., one fable). The view is partially obstructed by a "Find and Replace" dialog box, showing that 13 cells (fables) contain the word "animal" (i.e., they contain the string " animal ", with leading and trailing spaces) at least once.
(You may need to enlarge your view of the image to see this clearly.)
The last thing I'm going to show you, is what happens when I use the
COUNTIF function. I think it may be key to why I can't get this to work, because as soon as I add the range from the needed column,
before I even press Enter,
#VALUE!; #VALUE!; … next to the range.
It looks like this:
When I press Enter, I get the "0" you see for the cells in that column.
What am I doing wrong? How can I count the number of fables in which each word appears (at least once)?
If you can't tell from the screenshots, I'm using Excel 2007.