Using VBA To Identify Repeated Words In A Passage Of Text

When you’re focused on writing an article it’s easy to lose track of words that have been repeated. Typically, when you’re writing about keywords or phrases there’s every chance those words will be repeated several times.

But, with Excel and VBA we can report on a passage of text to find any repetitions.

Turning The Text Into An Array

For example, we’ll use the opening two paragraphs of this article.

First, we’ll turn the text – which we’ve saved into a string variable called txt – into an array by using the split function with a space as the delimiter. We’ll need to add a leading and trailing space to give the first and last words equality.

  myTxt = "" & txt & ""

all words = Split (myText, “”)

‘count

all words count = UBound (all words) + 1

Remember that in VBA arrays have a starting base of 0 unless you use the option base statement to declare it as 1.

Now we need to loop through each word in the array and see if it is used more than once throughout the text. We can do that by creating another array but using the search word as the delimiter:

  For x = 0 To UBound (allWords)

word = all words (x)

We can create the new array here and use unbound to calculate how many times the word appears in the text; Because of the extra spaces, we added it’s just a straight count. We’ve added spaces around the search word to take into account words within words, for example, “often” and “of”.

  ct = UBound (Split (myText, "" & word & ""))

Finally, if the count is greater than 1, we’ll write the result to the immediate window. We’ve used the string repeated to record any repetitions so we only report on a repetition once using the instr function.

  If ct> 1 And InStr (repeated, word) = 0 Then 
  debug.Print word & "" & ct 
  repeated = repeated & word 
  End If 
  Next

Here’s the final report:

  you're 2 
  on 3 
  writing 2 
  an 5 
  to 2 
  of 2 
  words 3 
  be 2 
  repeated 2

Enhancements And Problems With The Code

Some issues to think about with this code might include the following:

• Dealing with commas and full stops
• Only wanting to compare words of a certain length
• Comparing phrases and combinations of words

Variations of the code could cover phrases or multiple word searches, and length could be similarly restricted. The problem with punctuation such as commas and full stops is that any code would view “hello” and “hello,” as two different words, so any commas and full stops might have to be removed with the replace function before running the code.

Summary

This code snippet is an example of using VBA to solve a problem seemingly unrelated to rows and columns. It’s another reason to find out more about VBA and the numerous ways it can improve your productivity.

Source by Andy L Gibson