Excel:在进行部分匹配时，在单个单元格中返回多个匹配项 [英] Excel: Return multiple matches in a single cell while doing a partial match

问题描述

我正在处理具有两列的文件.第一列包含简单的三个单词的句子.第二个有单字关键字.

I am working on a file that has two columns. The first column has simple three word sentences. The second one has single word keywords.

我希望能够搜索第一列，找到所有具有特定关键字的句子，并在关键字旁边将它们作为分隔值列出.

I’d like to be able search the first column, find all sentences that have a specific keyword and list them as delimited values next to the keyword.

假设以竖线("|")作为分隔符，我将得到以下内容:

Assuming a pipe ("|") as a delimiter, I’d get something like this:

First Column
Very blue sky.
Red sky tonight. 
Blue sky forever. 
My red car. 
Red red red.

第二列如下:

Second Column
Blue
Red

所需的解决方案(第一栏中有2列，蓝色和红色)

Desired Solution (has 2 columns, Blue and Red are in the first column)

Second Column         Results Column
Blue                  Very blue sky. | Blue sky forever. 
Red                   Red sky tonight. | My red car. | Red red red.

谢谢！

推荐答案

这是一种实现方法.

1. 通过按ALT + F11键打开Visual Basic编辑器(VBE).
2. 使用插入>>模块插入新模块

3. 在代码窗格中粘贴以下代码.

1. Open Visual Basic Editor (VBE) by pressing ALT+F11 key.
2. Insert a new module using Insert >> Module

3. Paste below code in the code pane.

Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range

If strDelimiter = "" Then strDelimiter = "|"
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If

For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next

If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))

End Function

然后，您可以像其他任何正常功能一样在工作表中使用此功能

Then you can use this function in sheet like any other normal function e.g.

=ConcatPartLookUp(B2,A2:A6)

请注意，我提供了另外两个可选参数，从长远来看，它们可能会很有用.如果要使其区分大小写并传递不同的定界符，请说#"，则需要使用:

Please note I have provided two more optional arguments which may prove useful in the long run. If you want to make it case sensitive and pass a different delimiter say "#" then you need to use:

=ConcatPartLookUp(B2,A2:A6,"#",TRUE)

这篇关于Excel:在进行部分匹配时，在单个单元格中返回多个匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！