Verifique se a string sub na célula corresponde a outra string do intervalo de células

Question

Eu tenho um intervalo de células com esses valores de string:

A1: text1
A2: text2
A3: text3

E uma coluna com esses outros valores de string:

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

Tenho que verificar se o texto na coluna A é uma subcadeia do texto na coluna B.
Se sim, defina na coluna C o texto da coluna A.

Assim:

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

    

Answer 1

Experimente a seguinte macro pequena:

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