检查单元格中的子字符串是否与单元格范围内的其他字符串匹配

检查单元格中的子字符串是否与单元格范围内的其他字符串匹配

我有一系列包含以下字符串值的单元格:

A1: text1
A2: text2
A3: text3

还有一个包含以下其他字符串值的列:

B1: text1 text2 sampletext  
B2: text2 text3 sampletext    
B3: text3 sampletext    
B4: text1 sampletext    
B5: text1 sampletext

我必须检查 A 列中的文本是否是 B 列中文本的子字符串。
如果是,则在 C 列中设置 A 列中的文本。

像这样:

C1: text1 text2
C2: text2 text3
C3: text3
C4: text1
C5: text1

答案1

尝试以下小宏:

Sub BuildList()
    Dim N As Long, M As Long
    Dim i As Long, s1 As String, s2 As String

    N = Cells(Rows.Count, "A").End(xlUp).Row
    M = Cells(Rows.Count, "B").End(xlUp).Row
    ReDim wordlist(1 To N) As String

    For i = 1 To N
        wordlist(i) = Cells(i, 1)
    Next i
    For i = 1 To M
        ary = Split(Cells(i, "B"), " ")
        For j = LBound(ary) To UBound(ary)
            For k = 1 To N
                If wordlist(k) = ary(j) Then
                    Cells(i, "C") = Cells(i, "C") & " " & wordlist(k)
                End If
            Next k
        Next j
    Next i
End Sub

相关内容