Get In Touch
606 Tower A Plot, I-Thum Tower, Sector 62, Noida, Uttar Pradesh, IN
Ph: +91 8383.033.985
Work Inquiries
Ph: +91 8383.033.985

Utilizing VBA To Determine Repeated Phrases In A Passage Of Textual content


Once you're centered on writing an article it's straightforward to lose observe of phrases which have been repeated. Sometimes, while you're writing about key phrases or phrases there's each probability these phrases might be repeated a number of occasions.

However, with Excel and VBA we are able to report on a passage of textual content to search out any repetitions.

Turning The Textual content Into An Array

For an instance we'll use the opening two paragraphs of this text.

First, we'll flip the textual content – which we've saved right into a string variable referred to as txt – into an array through the use of the cut up operate with an area because the delimiter. We'll want so as to add a number one and trailing area to present the primary and final phrases equality.

  myTxt = "" & txt & "" 

allWords = Break up (myText, "")


allwordsCount = UBound (allWords) + 1

Do not forget that in VBA arrays have a beginning base of 0, until you employ the choice base assertion to declare it as 1.

Now we have to loop by every phrase within the array, and see whether it is used greater than as soon as all through the textual content. We are able to try this by creating one other array however utilizing the search phrase because the delimiter:

  For x = Zero To UBound (allWords) 

phrase = allWords (x)

We are able to create the brand new array right here and use ubound to calculate what number of occasions the phrase seems within the textual content; Due to the additional areas we added it's only a straight rely. We've added areas across the search phrase to take into consideration phrases inside phrases, for instance “usually” and “of”.

  ct = UBound (Break up (myText, "" & phrase & "")) 

Lastly, if the rely is bigger than 1, we'll write the end result to the fast window. We've used the string repeated to report any repetitions so we solely report on a repetition as soon as utilizing the instr operate.

  If ct> 1 And InStr (repeated, phrase) = Zero Then 
debug.Print phrase & "" & ct
repeated = repeated & phrase
Finish If

Right here's the ultimate report:

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

Enhancements And Issues With The Code

Some points to consider with this code may embody the next:

  • Coping with commas and full stops
  • Solely wanting to match phrases of a sure size
  • Evaluating phrases and mixtures of phrases

Variations of the code may cowl phrases or a number of phrase searches, and size might be equally restricted. The issue with punctuation akin to commas and full stops is that any code would view “hey” and “hey,” as two completely different phrases, so any commas and full stops might need to be eliminated with the substitute operate earlier than operating the code.


This code snippet is an instance of utilizing VBA to unravel an issue seemingly unrelated to rows and columns. It's another excuse to search out out extra about VBA and the quite a few methods it could enhance your productiveness.

Supply by Andy L Gibson

Post a comment

Your email address will not be published. Required fields are marked *