CODEWITHSUNDEEP
excelexcel-formulauser-defined-functions
Louhzer
Louhzer

Reputation: 33

Extract partial matches string(words) between 2 columns in Excel

I have two columns like below, I need to extract a partial word that matches between the 2 columns and put this in a 3rd columns.

      Name1                              Name2
RED CURRY CHICKEN                 GREEN CURRY CHICKEN
BEEF WITH MINT LEAVES             BEEF WITH BASIL LEAVES
SWEET AND SOUND PORK              BACON AND EGG
FRIED RICE                        FRIED RICE

My expected result 

      Name1                              Name2                     Partial Matches
RED CURRY CHICKEN                 GREEN CURRY CHICKEN               CHICKEN
BEEF WITH MINT LEAVES             BEEF WITH BASIL LEAVES            BEEF WITH LEAVES
SWEET AND SOUND PORK              BACON AND EGG                     AND
FRIED RICE                        FRIED RICE                        FRIED RICE

Upvotes: 0

Views: 90

Answers (1)

user4039065
user4039065

Reputation:

To do this with a formula is inefficient; you need vba for a sub procedure or user defined function.

Function friedRice(str1 As String, str2 As String)
    Dim w As Long, words As Variant, tmp As String

    words = Split(str1, Chr(32))

    For w = LBound(words) To UBound(words)
        If Not IsError(Application.Match(words(w), Split(str2, Chr(32)), 0)) Then
            tmp = tmp & Chr(32) & words(w)
        End If
    Next w

    friedRice = Trim(tmp)

End Function

enter image description here

Upvotes: 1

Related Questions