Excel - Compare Two Cells & Return What’s Common Between the two strings and delete the variation

Compare Two Cells & Return What’s Common Between the two strings and delete the variation

0 Shares
0
0
0
0
0
0
0

Function StringMatch(ByVal sMaster As String, ByVal sSlave As String, Optional bMatchCase = False, Optional sDelimiter = " ") As String

Dim asMast() As String, asSlav() As String
Dim lWordLoop As Long
Dim sTemp As String

If Not bMatchCase Then
  sMaster = UCase(sMaster)
  sSlave = UCase(sSlave)
End If

asMast = Split(sMaster, sDelimiter)
asSlav = Split(sSlave, sDelimiter)

sTemp = ""

For lWordLoop = LBound(asMast) To UBound(asMast)
  If Not IsError(Application.Match(asMast(lWordLoop), asSlav, 0)) Then
    sTemp = sTemp & asMast(lWordLoop) & sDelimiter
  End If
Next lWordLoop

If Len(sDelimiter) > 0 And Len(sTemp) > 0 Then
  sTemp = Left(sTemp, Len(sTemp) - Len(sDelimiter))
End If

StringMatch = sTemp

End Function

To insert this:

1. Open your workbook
2. Press Alt-F11 to open the VB editor
3. From the insert menu select “Module” (not “Class module”)
4. A blank edit window will open, paste the code above directly into it.
5. Close the VB editor to return to your workbook

You will now have a user defined function you can use in your workbook, StringMatch, so the formula:

=StringMatch(A3,A4)

Will return the words that are common to both cells A3 and A4.

Hope this is what you’re looking for.

Reference Click Here