Easy way to find repetation of words in Excel

“Most humans use 2000 words in a language”, says an old adage. Although I do not agree with it, I still believe that word analysis is essential for everyone.

Suppose you are learning a language, how great would be it if you knew the keywords that most newspapers use. Or, if you are a technical writer who is tasked to write a highly specialized content that demands a specific vocabulary, in such cases, word analysis can help.

Even if you read newspapers, you are likely to find a few words that you may not pay attention to, but if you did, then you could improve your vocabulary. Or say if you are a content head, and want to check content for plagiarism, you could find the frequency of the words used, and make out if the content is plagiarized.

This article will help you understand how this can be done — in simple steps. All you need is Microsoft Excel installed on your computer.

1. Start your excel program.

2. Copy Any string, or piece of string that you want to perform a repetition check for. (I have posted the image just below step 3)

3. Go to “Developer” option on your Excel and click on Macros (As located in the image below)
4. Once you click there, assign macro a name (like, Find String Macro), and click on ‘create’ which is located on the right-hand side of the dialog box.

5. You will see a dialogue box, which would be blank, paste the code below.

Sub Fable()
Dim BigString As String, I As Long, J As Long, K As Long
         BigString = “”
For Each r In Selection
BigString = BigString & ” ” & r.Value
Next r
BigString = Trim(BigString)
ary = Split(BigString, ” “)
Dim cl As Collection
Set cl = New Collection
For Each a In ary
On Error Resume Next
cl.Add a, CStr(a)
Next a

For I = 1 To cl.Count
v = cl(I)
Cells(I, “B”).Value = v
J = 0
For Each a In ary
If a = v Then J = J + 1
Next a
Cells(I, “C”) = J
Next I
End Sub

6. Now, press Alt+F11 or click Excel icon on the top left (below file menu).

7. Select the cells that you want to run text repetition check for, and click on macros again, and select the macro that you just created (with the name you assigned in step 4).

Say you have data in cells A1 to A4, so, you need to first select all the four cells and then click on macro (So that it excel knows where to run the string repetition check).

8. You are done! now you will see two rows, one with the word, and second with the number of repetition of the word in the excel file.

Now, if you are learning a language like Spanish or English,  pick up the words that you don’t know, and put them in your flashcard or some other repetition list.

Leave a Reply

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