|
Option Explicit
Sub TEST1()
Dim ar, i&, strPat$, strName$, aMatch As Object
ar = Range("F1", Cells(Rows.Count, "F").End(xlUp)).Value
For i = 2 To UBound(ar)
If Len(strPat) Then
strPat = strPat & "|" & ar(i, 1)
Else
strPat = ar(i, 1)
End If
Next i
ar = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
ar(1, 1) = "提取左侧单元格姓名"
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = strPat
For i = 2 To UBound(ar)
strName = ""
For Each aMatch In .Execute(ar(i, 1))
strName = strName & "、" & aMatch.Value
Next
ar(i, 1) = Mid(strName, 2)
Next i
End With
[C1].Resize(UBound(ar)) = ar
Beep
End Sub
|
|