代码之家 › 专栏 › 技术社区 › Louhzer

提取Excel中两列之间的部分匹配字符串(单词)

user-defined-functions excel-formula excel

0

Louhzer · 技术社区 · 8 年前

我有两列,如下所示,我需要提取一个与这两列匹配的部分单词,并将其放在第三列中。

      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

我的预期结果

      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

1 回复 | 直到 8 年前

1

user4039065 user4039065 8 年前

用公式来做这件事效率很低;子过程或用户定义函数需要vba。

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