Private Sub CommandButton1_Click()
' Clear form and setup variables
Range("A3:C10001").ClearContents
myVal = ""
myLen = 0
' Copy the original text to a temp range (so you don't mess up the original)
Range("B3").Value = Range("A1").Value
' Remove all non-standard characters (punctuation, etc.), so they aren't counted as part of a word
myVal = Range("B3").Value
myVal = Replace(myVal, ",", "")
myVal = Replace(myVal, ".", "")
myVal = Replace(myVal, ";", "")
myVal = Replace(myVal, ":", "")
myVal = Replace(myVal, "'", "")
myVal = Replace(myVal, "!", "")
myVal = Replace(myVal, "?", "")
myVal = Replace(myVal, "#", "")
myVal = Replace(myVal, "$", "")
myVal = Replace(myVal, "%", "")
myVal = Replace(myVal, "^", "")
myVal = Replace(myVal, "&", "")
myVal = Replace(myVal, "*", "")
myVal = Replace(myVal, "(", "")
myVal = Replace(myVal, ")", "")
myVal = Replace(myVal, "-", "")
myVal = Replace(myVal, "_", "")
myVal = Replace(myVal, "+", "")
myVal = Replace(myVal, "=", "")
myVal = Replace(myVal, "[", "")
myVal = Replace(myVal, "]", "")
myVal = Replace(myVal, "{", "")
myVal = Replace(myVal, "}", "")
myVal = Replace(myVal, "\", "")
myVal = Replace(myVal, "|", "")
myVal = Replace(myVal, "/", "")
myVal = Replace(myVal, "?", "")
myVal = Replace(myVal, "<", "")
myVal = Replace(myVal, ">", "")
myVal = Replace(myVal, "`", "")
myVal = Replace(myVal, "~", "")
Range("B3").Value = myVal
' Determine how many words there are in the statement
myLen = Len(myVal) - Len(Replace(myVal, " ", "")) + 1
' Make sure more than one word was entered in A1
If myLen <= 1 Then Exit Sub
' Break the text apart into individual words
myRow = 4
For cnt = 1 To myLen
Range("A" & myRow).Formula = "=IF(ISERROR(LEFT(B" & myRow - 1 & ",FIND("" "",B" & myRow - 1 & ")-1)),B" & myRow - 1 & ",LEFT(B" & myRow - 1 & ",FIND("" "",B" & myRow - 1 & ")-1))"
Range("B" & myRow).Formula = "=IF(ISERROR(RIGHT(B" & myRow - 1 & ",LEN(B" & myRow - 1 & ")-LEN(A" & myRow & ")-1)),"""",RIGHT(B" & myRow - 1 & ",LEN(B" & myRow - 1 & ")-LEN(A" & myRow & ")-1))"
Range("C" & myRow).Value = myRow
myRow = myRow + 1
Next cnt
' Convert to text
Range("A4:A" & myRow).Value = Range("A4:A" & myRow).Value
Range("B3:B" & myRow).ClearContents
' Count up instances of each word
myRow = 4
For cnt = 1 To myLen
Range("B" & myRow).Formula = "=COUNTIF(A:A,A" & myRow & ")"
myRow = myRow + 1
Next cnt
' Finish calculations, clean up form
Range("B4:B" & myRow).Value = Range("B4:B" & myRow).Value
myRow = 4
For cnt = 1 To myLen
myWord = Range("A" & myRow).Value
If Evaluate("=countif(A$3:A" & myRow - 1 & ",""" & myWord & """)") > 0 Then
Range("A" & myRow & ":C" & myRow).ClearContents
End If
myRow = myRow + 1
Next cnt
Application.Wait (Now + TimeValue("00:00:00"))
Range("A4:C" & myRow).Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("C4:C" & myRow).ClearContents
End Sub