EXCEL 匹配正則表達式

粘貼代碼後按f5即可在c列得到結果

Sub Test()

atr = Range("a65536").End(xlUp).Row

btr = Range("b65536").End(xlUp).Row

a = Range("a1:a" & atr).Value

b = Range("b1:b" & btr).Value

ReDim c(1 To atr, 1 To 1)

Set reg = CreateObject("vbscript.regexp")

With reg

.Global = True

.IgnoreCase = True

For ar = 1 To atr

For br = 1 To btr

.Pattern = b(br, 1)

If .Test(a(ar, 1)) Then

c(ar, 1) = "匹配"

Exit For

End If

Next

Next

End With

Range("c1:c" & atr) = c

Set reg = Nothing

End Sub