title: VBA - How do I compare text in Excel cells to see if same words are found, exclusing punctuation?
body: <p>I have numerous rows of Excel cells which contain a string of words, there are commas and hyphens in some, but general text with just spaced in others. I want to compare each cell with another cell to check if any of the words match/are duplicates. </p>
  
  <p>I've found some VBA codes - but it only helps with some cells and not others and I'm not sure why. How do I remove punctuation so the results come up showing the duplicated values (if there are no values - a statement of No Match)</p>
  
  <p>The code I've been using:</p>
  
  <pre><code>Function DupeWord(str1 As String, str2 As String) As String
    Dim vArr1
    Dim vArr2
    Dim vTest
    Dim lngCnt As Long
  
    vArr1 = Split(Replace(str1, " ", vbNullString), ",")
    vArr2 = Split(Replace(str2, " ", vbNullString), ",")
    On Error GoTo strExit
  
    For lngCnt = LBound(vArr1) To UBound(vArr1)
      vTest = Application.Match(vArr1(lngCnt), vArr2, 0)
      If Not IsError(vTest) Then DupeWord = DupeWord &amp; vArr1(lngCnt) &amp; ", "
    Next lngCnt
  
    If Len(DupeWord) &gt; 0 Then
      DupeWord = Left$(DupeWord, Len(DupeWord) - 2)
    Else
  strExit:
      DupeWord = "No Matches!"
    End If
  End Function
  </code></pre>