excel中用正则匹配_excel 利用正则表达式匹配工作表中的数据

excel 利用正则表达式匹配工作表中的数据

sheet1中A列放需要匹配的数据

sheet2中A列放正则表达式，可以是多个，但至少一个

匹配到了则在sheet1中C列对应行显示相关标记内容，比如本案例中显示1111

Sub Test()

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

btr = Worksheets("Sheet2").Range("a65536").End(xlUp).Row

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

b = Worksheets("Sheet2").Range("a1:a" & 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

If btr = 1 Then

.Pattern = b

Else

.Pattern = b(br, 1)

End If

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

c(ar, 1) = "1111"

Exit For

End If

Next

Next

End With

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

Set reg = Nothing

End Sub