CODEWITHSUNDEEP
excelvba
manny
manny

Reputation: 11

Compare text within two Cells in Excel VBA

I am new at VBA Programming and was hoping if someone can help with this. I have been reading some other post but too complicated for me to understand at my level.

I am trying to compare two cells in Excel and trying to match words within both of the Cells and see if there are any words matching between them. (see picture for reference)

Upvotes: 0

Views: 1186

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Try this small UDF():

Public Function WhatsInCommon(A As String, B As String) As String
    arya = Split(LCase(A), " ")
    aryb = Split(LCase(B), " ")

    WhatsInCommon = ""
    For Each aa In arya
        For Each bb In aryb
            If aa = bb Then
                If WhatsInCommon = "" Then
                    WhatsInCommon = aa
                Else
                    WhatsInCommon = WhatsInCommon & "," & aa
                End If
            End If
        Next bb
    Next aa
End Function

For example:

enter image description here

Notes:

  1. assumes that a "word" is a string of characters encapsulated by a space
  2. converts to lower case

EDIT#1:

This version will only consider words greater than two character:

Public Function WhatsInCommon(A As String, B As String) As String
    arya = Split(LCase(A), " ")
    aryb = Split(LCase(B), " ")

    WhatsInCommon = ""
    For Each aa In arya
        If Len(aa) > 2 Then
            For Each bb In aryb
                If aa = bb Then
                    If WhatsInCommon = "" Then
                        WhatsInCommon = aa
                    Else
                        WhatsInCommon = WhatsInCommon & "," & aa
                    End If
                End If
            Next bb
        End If
    Next aa
End Function

Upvotes: 0

Related Questions