Text Analysis

[AndrewD04]

Hi, Does anyone know how to solve this.
I have a lot of cells with free text in and I want excel to list every unique word and how often that word appears

 

[Jerry Sullivan]

Hi Andrew,

One way would be to use a Dictionary object to count each unique word. The trickier part of the problem is define what you want to consider a unique "word" for the purpose of this application.

The simplest definition to code would be to split the text strings up using just the space character as the delimiter. Beyond that you would need to consider the handling of punctuation, special characters, numbers and case sensitivity. Your decision on how you want those handled could make the coding needed to parse "words" significantly more complex.

 

[thisoldman]

... The trickier part of the problem is define what you want to consider a unique "word" for the purpose of this application.

The simplest definition to code would be to split the text strings up using just the space character as the delimiter. Beyond that you would need to consider the handling of punctuation, special characters, numbers and case sensitivity. Your decision on how you want those handled could make the coding needed to parse "words" significantly more complex.

Regular expressions might make this a bit easier: use a capturing group, perhaps "(\w+)". That removes punctuation, but "boy's" becomes two words: "boy" and the single letter "s".

Some examples of the tricky parts that Jerry Sullivan mentioned, depending on the body of text and your purpose:

Proper nouns: "Don", the river or the man's name versus "don", meaning to put on. "Mike", the name, and "mike" for a microphone. Is "Los Angeles" one word or two?

Plurals: If you decide to treat the singular and plural forms of nouns as the same word, making "doll" and "dolls" count as repeats of one word, how do you handle "man" and "men", "child" and "children", "index" and "indices", etc? The rules for plurals are strange. In "cherry" the final "y" becomes "ie" in "cherries", but "key" simply adds an "s" after the "y". One "knife" becomes two "knives" and one "dwarf" becomes two "dwarfs"—unless you're Tolkien. The plurals of "ax" and "axis" are both "axes".