我有一系列包含以下字符串值的单元格:
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