700字范文 > excel中用正则匹配_如何在Microsoft Excel中使用正则表达式（正则表达式）在单元格和循环中...

excel中用正则匹配_如何在Microsoft Excel中使用正则表达式（正则表达式）在单元格和循环中...

时间：2020-02-07 07:22:47

要直接在Excel公式中使用正则表达式，以下UDF(用户定义的函数)可能会有所帮助。它或多或少直接将正则表达式功能公开为excel函数。

这个怎么运作

它需要2-3个参数。

要使用正则表达式的文本。

正则表达式。

格式字符串，指定结果的外观。它可以包含Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = "\$(\d+)"

End With

With outReplaceRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

End With

Set inputMatches = inputRegexObj.Execute(strInput)

If inputMatches.Count = 0 Then

regex = False

Else

Set replaceMatches = outputRegexObj.Execute(outputPattern)

For Each replaceMatch In replaceMatches

replaceNumber = replaceMatch.SubMatches(0)

outReplaceRegexObj.Pattern = "\$" & replaceNumber

If replaceNumber = 0 Then

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)

Else

If replaceNumber > inputMatches(0).SubMatches.Count Then

'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."

regex = CVErr(xlErrValue)

Exit Function

Else

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))

End If

regex = outputPattern

End If

End Function,regex,$2等。 $0是整个比赛，$1及以上对应于正则表达式中的各个匹配组。默认为$0。

一些例子

提取电子邮件地址：

=regex("Peter Gordon: some@, 47", "\w+@\w+\.\w+")

=regex("Peter Gordon: some@, 47", "\w+@\w+\.\w+", "$0")

结果：Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = "\$(\d+)"

End With

With outReplaceRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

End With

Set inputMatches = inputRegexObj.Execute(strInput)

If inputMatches.Count = 0 Then

regex = False

Else

Set replaceMatches = outputRegexObj.Execute(outputPattern)

For Each replaceMatch In replaceMatches

replaceNumber = replaceMatch.SubMatches(0)

outReplaceRegexObj.Pattern = "\$" & replaceNumber

If replaceNumber = 0 Then

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)

Else

If replaceNumber > inputMatches(0).SubMatches.Count Then

'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."

regex = CVErr(xlErrValue)

Exit Function

Else

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))

End If

regex = outputPattern

End If

End Function

提取几个子串：

=regex("Peter Gordon: some@, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

结果：Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = "\$(\d+)"

End With

With outReplaceRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

End With

Set inputMatches = inputRegexObj.Execute(strInput)

If inputMatches.Count = 0 Then

regex = False

Else

Set replaceMatches = outputRegexObj.Execute(outputPattern)

For Each replaceMatch In replaceMatches

replaceNumber = replaceMatch.SubMatches(0)

outReplaceRegexObj.Pattern = "\$" & replaceNumber

If replaceNumber = 0 Then

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)

Else

If replaceNumber > inputMatches(0).SubMatches.Count Then

'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."

regex = CVErr(xlErrValue)

Exit Function

Else

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))

End If

regex = outputPattern

End If

End Function

将单个单元格中的组合字符串拆分为多个单元格中的组件：

=regex("Peter Gordon: some@, 47", "^(.+): (.+), (\d+)$", "$" & 1)

=regex("Peter Gordon: some@, 47", "^(.+): (.+), (\d+)$", "$" & 2)

结果在：Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = "\$(\d+)"

End With

With outReplaceRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

End With

Set inputMatches = inputRegexObj.Execute(strInput)

If inputMatches.Count = 0 Then

regex = False

Else

Set replaceMatches = outputRegexObj.Execute(outputPattern)

For Each replaceMatch In replaceMatches

replaceNumber = replaceMatch.SubMatches(0)

outReplaceRegexObj.Pattern = "\$" & replaceNumber

If replaceNumber = 0 Then

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)

Else

If replaceNumber > inputMatches(0).SubMatches.Count Then

'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."

regex = CVErr(xlErrValue)

Exit Function

Else

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))

End If

regex = outputPattern

End If

End Function regex ......

如何使用

要使用此UDF，请执行以下操作(大致基于此Microsoft页面。它们有一些很好的附加信息！)：

在启用宏的文件('.xlsm')中的Excel中，按Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = "\$(\d+)"

End With

With outReplaceRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

End With

Set inputMatches = inputRegexObj.Execute(strInput)

If inputMatches.Count = 0 Then

regex = False

Else

Set replaceMatches = outputRegexObj.Execute(outputPattern)

For Each replaceMatch In replaceMatches

replaceNumber = replaceMatch.SubMatches(0)

outReplaceRegexObj.Pattern = "\$" & replaceNumber

If replaceNumber = 0 Then

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)

Else

If replaceNumber > inputMatches(0).SubMatches.Count Then

'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."

regex = CVErr(xlErrValue)

Exit Function

Else

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))

End If

regex = outputPattern

End If

End Function以打开Microsoft Visual Basic for Applications Editor。

将VBA引用添加到Regular Expressions库(从Portland Runners ++回答中无耻地复制)：点击工具 - ＆gt; 参考文献(请原谅德国截图)

在列表中找到Microsoft VBScript Regular Expressions 5.5并勾选旁边的复选框。

单击确定。

单击“插入模块”。如果为模块指定了不同的名称，请确保模块与下面的UDF名称不同(例如，命名模块Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = "\$(\d+)"

End With

With outReplaceRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

End With

Set inputMatches = inputRegexObj.Execute(strInput)

If inputMatches.Count = 0 Then

regex = False

Else

Set replaceMatches = outputRegexObj.Execute(outputPattern)

For Each replaceMatch In replaceMatches

replaceNumber = replaceMatch.SubMatches(0)

outReplaceRegexObj.Pattern = "\$" & replaceNumber

If replaceNumber = 0 Then

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)

Else

If replaceNumber > inputMatches(0).SubMatches.Count Then

'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."

regex = CVErr(xlErrValue)

Exit Function

Else

outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))

End If

regex = outputPattern

End If

End Function，函数regex导致#NAME！错误)。

在中间的大文本窗口中插入以下内容：

Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp

Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object

Dim replaceNumber As Integer

With inputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = matchPattern

End With

With outputRegexObj

.Global = True

.MultiLine = True

.IgnoreCase = False