我只需要从 Excel 电子表格中提取电子邮件。我在 StackOverflow 上找到了一些示例 VBA 代码link https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops，礼貌地波特兰跑步者 https://stackoverflow.com/users/2521004/portland-runner.

我创建了一个 Excel 模块，它似乎工作正常，只是它只将地址的第一个大写字符返回到单元格中，并且忽略了电子邮件。

例如：

Text                                    | Result
----------------------------------------|------------------------------
My email address is [email protected] /cdn-cgi/l/email-protection   | My email address is  
Yes  [email protected] /cdn-cgi/l/email-protection                  | Yes  A

下面是我正在使用的代码：

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

我没有足够的 VBA 经验来真正诊断这里可能发生的情况，希望有人能够发现我做错了什么。

工作代码

Function simpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String


strPattern = "[A-Za-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-zA-Z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = ""

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        Set matches = regEx.Execute(strInput)
        simpleCellRegex = matches(0).Value
    Else
        simpleCellRegex = "Not matched"
    End If
End If
End Function

当您返回 strInput 时，您只会得到与输入相同的字符串。 您需要返回使用 RegExp 找到的值。

Try

Set matches = regEx.Execute(strInput)
simpleCellRegex = matches(1).Value

代替

simpleCellRegex = regEx.Replace(strInput, strReplace)