正则表达式提取电子邮件 [英] RegEx to extract email

问题描述

我只需要从Excel中的电子表格中提取电子邮件.我在此StackOverflow Portland Runner .

I need to extract only the email from a spreadsheet in Excel. I've found some example VBA code here at this StackOverflow link, courtesy of Portland Runner.

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

I created an Excel module and it seems to be working fine, except it only returns the first uppercase character of the address into the cell and it's ignoring the email.

例如:

Text                                    | Result
----------------------------------------|------------------------------
My email address is [e-mail]   | My email address is  
Yes  [e-mail]                  | Yes  A

以下是我正在使用的代码:

Below is the code I'm using:

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经验来真正诊断这里可能发生的情况，希望有人能够发现我在做错什么.

I do not have enough experience with VBA to really diagnose what might be happening here, hopefully someone will be able to spot what I'm doing wrong.

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找到的Value.

When You return strInput You just get the same string as the input. You need to return Value that has been found using RegExp.

尝试

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

代替

simpleCellRegex = regEx.Replace(strInput, strReplace)

这篇关于正则表达式提取电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！