提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何从文本字符串中快速提取电子邮件地址?

将网站的某些电子邮件地址导入Excel工作表时,总是包含不相关的文本,但现在只需从文本字符串中提取纯电子邮件地址(请参阅以下屏幕截图)。 你怎么能迅速从电池文本中获得电子邮件地址?

DOC-提取物 -  emails1 -2 DOC-提取物 -  emails2

使用公式从文本字符串中提取电子邮件地址

用用户定义的函数从文本字符串中提取电子邮件地址

用VBA代码从文本字符串中提取电子邮件地址

使用Kutools for Excel从文本字符串中提取电子邮件地址


从文本字符串中提取电子邮件地址:

Kutools for Excel 提取电子邮件地址 可以帮助您快速方便地从文本字符串中提取电子邮件地址。

doc提取电子邮件-1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 使用公式从文本字符串中提取电子邮件地址


这里我向你介绍一个很长的公式,用于从Excel中的文本中仅提取电子邮件地址。 请做如下操作:

1。 在相邻的单元格B1中,输入此公式 = TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(“”,A1&“”,FIND(“@”,A1))-1),“”,REPT(“”,LEN(A1))),LEN A1))).

DOC-提取物 -  emails3

2。 然后按 输入 键,然后选择单元格B1,然后将填充句柄拖到要包含此公式的范围。 并且该范围内的电子邮件地址已从文本字符串中提取。 看截图:

DOC-提取物 -  emails4

备注:

1。 电子邮件地址后面的标点符号也将被提取。

2。 如果单元格不包含电子邮件地址,则公式将显示错误值。

3。 如果单元格中有多个电子邮件地址,则公式只会提取第一个地址。


箭头蓝色右泡 用用户定义的函数从文本字符串中提取电子邮件地址

除了上述公式外,用户定义函数还可以帮助您从文本字符串中获取电子邮件地址。

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并在模块窗口中粘贴以下宏。

Function ExtractEmailFun(extractStr As String) As String
'Update 20130829
Dim CharList As String
On Error Resume Next
CheckStr = "[A-Za-z0-9._-]"
OutStr = ""
Index = 1
Do While True
    Index1 = VBA.InStr(Index, extractStr, "@")
    getStr = ""
    If Index1 > 0 Then
        For p = Index1 - 1 To 1 Step -1
            If Mid(extractStr, p, 1) Like CheckStr Then
                getStr = Mid(extractStr, p, 1) & getStr
            Else
                Exit For
            End If
        Next
        getStr = getStr & "@"
        For p = Index1 + 1 To Len(extractStr)
            If Mid(extractStr, p, 1) Like CheckStr Then
                getStr = getStr & Mid(extractStr, p, 1)
            Else
                Exit For
            End If
        Next
        Index = Index1 + 1
        If OutStr = "" Then
            OutStr = getStr
        Else
            OutStr = OutStr & Chr(10) & getStr
        End If
    Else
        Exit Do
    End If
Loop
ExtractEmailFun = OutStr
End Function

3。 然后保存代码并输入公式 = ExtractEmailFun(A1) 在相邻的空白单元格中,看截图:

DOC-提取物 -  emails5

4。 然后按 输入 键,选择单元格B1,然后将填充句柄拖到需要公式的范围。 所有电子邮件地址都已从单元格文本中提取。 看截图:

DOC-提取物 -  emails6

备注:

1。 如果单元格没有电子邮件地址,则会显示空白单元格。

2。 如果单元格中有多个电子邮件地址,则所有电子邮件都将被提取。


箭头蓝色右泡 用VBA代码从文本字符串中提取电子邮件地址

如果你觉得上面的公式对你来说很麻烦,下面的VBA代码可以帮助你在一个地方提取电子邮件地址。

1。 按住 ALT + F11 键,并打开一个 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并粘贴在下面的宏 模块窗口.

VBA:从文本字符串中提取电子邮件地址

Sub ExtractEmail()
'Update 20130829
Dim WorkRng As Range
Dim arr As Variant
Dim CharList As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
arr = WorkRng.Value
CheckStr = "[A-Za-z0-9._-]"
For i = 1 To UBound(arr, 1)
    For j = 1 To UBound(arr, 2)
        extractStr = arr(i, j)
        outStr = ""
        Index = 1
        Do While True
            Index1 = VBA.InStr(Index, extractStr, "@")
            getStr = ""
            If Index1 > 0 Then
                For p = Index1 - 1 To 1 Step -1
                    If Mid(extractStr, p, 1) Like CheckStr Then
                        getStr = Mid(extractStr, p, 1) & getStr
                    Else
                        Exit For
                    End If
                Next
                getStr = getStr & "@"
                For p = Index1 + 1 To Len(extractStr)
                    If Mid(extractStr, p, 1) Like CheckStr Then
                        getStr = getStr & Mid(extractStr, p, 1)
                    Else
                        Exit For
                    End If
                Next
                Index = Index1 + 1
                If outStr = "" Then
                    outStr = getStr
                Else
                    outStr = outStr & Chr(10) & getStr
                End If
            Else
                Exit Do
            End If
        Loop
        arr(i, j) = outStr
    Next
Next
WorkRng.Value = arr
End Sub

3。 然后按 F5 键来运行此代码,并且您应该在弹出的对话框中选择您想要使用VBA的范围,请参阅屏幕截图:

DOC-提取物 -  emails7

4。 然后点击 OK,并且已从所选文本字符串中提取电子邮件地址。 查看屏幕截图:

DOC-提取物 -  emails8 -2 DOC-提取物 -  emails9

备注:

1。 如果单元格没有电子邮件地址,则会显示空白单元格。

2。 如果单元格中有多个电子邮件地址,所有电子邮件都将被提取。

3。 提取的电子邮件将覆盖原始数据,因此如果需要,最好先备份数据。


箭头蓝色右泡 通过单击Kutools for Excel从文本字符串中提取电子邮件地址

上述方法对于我们的Excel初学者来说看起来有些复杂,在这里,我可以推荐一个快速简单的工具 - Kutools for Excel,其 提取电子邮件地址 实用程序,您可以毫不费力地从文本字符串中提取电子邮件地址。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

如果你已经安装 Kutools for Excel请按照以下步骤进行:

1。 选择包含文本字符串的单元格。

2。 点击 Kutools > 文本 > 提取电子邮件地址,看截图:

DOC-提取物 -  emails10-10

3。 和 提取电子邮件地址 弹出对话框,选择一个你想放置结果的单元格,看截图:

DOC-提取物 -  emails9

4。 然后点击 OK 按钮,所有的电子邮件地址都已经从文本字符串中提取出来,请参阅截图:

DOC-提取物 -  emails9

点击下载并免费试用Kutools for Excel Now!


箭头蓝色右泡 演示:使用Kutools for Excel从文本字符串中提取电子邮件地址

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

如何从Excel中的多个电子邮件地址提取域?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Vivia Santos · 3 months ago
    Quando extraído mais que um email usando a macro, como separa-los depois usando uma outra célula ? Ou é possível extrair já separando ?
  • To post as a guest, your comment is unpublished.
    Jim Butler · 5 months ago
    Saved me hours of manual parsing. Thank you!
  • To post as a guest, your comment is unpublished.
    Ultraswift Network · 10 months ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    Cedric · 11 months ago
    Hi. This is a great job! I am sure that hundreds of people learned new stuff because of it. But if you just want to extract email addresses, you can use extractemailaddress.com . It seemed simple and quick, and I hav pasted the result in my excel spreadsheet. great!
  • To post as a guest, your comment is unpublished.
    mohit som · 1 years ago
    12 xyz john_hayden@microsoft.com 34abcd

    abcd 1234 abcd xyz john_hayden@microsoft.com

    how to find the mail id in this string.....
  • To post as a guest, your comment is unpublished.
    Ricardo Barreto · 2 years ago
    Great Add ON! Congratulations! :)
  • To post as a guest, your comment is unpublished.
    Liviu · 2 years ago
    Hello, great job! Very useful.
  • To post as a guest, your comment is unpublished.
    Helen · 2 years ago
    I'm not sure if my comment went through, so I'm sending it again.

    Can I use the above VB script to extract domain names only? I don't need the email addresses.

    Thanks
    • To post as a guest, your comment is unpublished.
      Louie Clay · 2 years ago
      [quote name="Helen"]I'm not sure if my comment went through, so I'm sending it again.

      Can I use the above VB script to extract domain names only? I don't need the email addresses.

      Thanks[/quote]

      This works to extract the domain name only for the first email address in a cell (here arbitrarily cell A1)

      =MID(A1,FIND("@",A1)+1,FIND(" ",RIGHT(A1,LEN(A1)-FIND("@",A1)),1))
  • To post as a guest, your comment is unpublished.
    Helen · 2 years ago
    This VB script is awesome. Can it be modified to extract only the domain.names?
  • To post as a guest, your comment is unpublished.
    Helen · 2 years ago
    This formula is fabulous; however, I need to extract the domain names only, not the entire email address. I'm not a VB expert and couldn't find out a way to modify to extract out only the domain name. Can someone assist with this?

    Thanks
  • To post as a guest, your comment is unpublished.
    Sheryl Moss · 2 years ago
    I'm using Excel 2007on a HP. If you have Melanie Brown in A1 and wish for it to read Melanie.Brown@gmail.com in the same cell, how do you accomplish this? I have a string of manes to do the same way. Will some one help me with this?
  • To post as a guest, your comment is unpublished.
    Raul · 2 years ago
    Awsome info`s! Thanks
  • To post as a guest, your comment is unpublished.
    Manuel · 3 years ago
    Extremely helpful. Thanks a lot!!
  • To post as a guest, your comment is unpublished.
    Louie Clay · 3 years ago
    If I want to extract only one email address from A1, this formula does so and reports only a blank, not an error, if A1 contains no email address. I find this an easier solution than trying to master all these scripts, and it costs nothing.

    =IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1))),"")
  • To post as a guest, your comment is unpublished.
    Navjot SINGH · 3 years ago
    Excellent, wonderfull. every one should must use..
  • To post as a guest, your comment is unpublished.
    Mark · 3 years ago
    since the formula has been a great help to me, I thought I'd share my experience. I run it against a list of html webscrapes which are sometimes so long that the formula errors out.

    According to wikipedia the maximum length of an email address is 254 characters so replacing the len(A1) portions with 256 improves the stability of the function:

    =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",256)),256))
  • To post as a guest, your comment is unpublished.
    anil · 3 years ago
    Dear Sir,
    How can i extract correct email ids from my email list example below

    a.hamilton@tradsingapore.com.sg
    aarabest@emirates.com.net.ae
    admin@countrywide.com.my
  • To post as a guest, your comment is unpublished.
    kelvin · 3 years ago
    i need more information how to extract emails
  • To post as a guest, your comment is unpublished.
    Manish · 4 years ago
    Brother, you are really genius.

    Below formula worked for me and saved manual intervention which used to take hours

    =TRIM(RIGHT(SUBSTITUTE(LEFT(H2,FIND (" ",H2&" ",FIND("@",H2))-1)," ", REPT(" ",LEN(H2))),LEN(H2)))


    God Bless you
  • To post as a guest, your comment is unpublished.
    Kiru · 4 years ago
    This is good! thank you.
  • To post as a guest, your comment is unpublished.
    The Chad · 4 years ago
    Thanks to the author of the original script; I went ahead and added a "; " separator in between multiple e-mail addresses.


    Function ExtractEmailFun(extractStr As String) As String
    'Update 20150723
    Dim CharList As String
    On Error Resume Next
    CheckStr = "[A-Za-z0-9._-]"
    ExtractEmailFun = ""
    Index = 1
    Do While True
    Index1 = VBA.InStr(Index, extractStr, "@")
    getStr = ""
    If Index1 > 0 Then
    For p = Index1 - 1 To 1 Step -1
    If Mid(extractStr, p, 1) Like CheckStr Then
    getStr = Mid(extractStr, p, 1) & getStr
    Else
    Exit For
    End If
    Next
    getStr = getStr & "@"
    For p = Index1 + 1 To Len(extractStr)
    If Mid(extractStr, p, 1) Like CheckStr Then
    getStr = getStr & Mid(extractStr, p, 1)
    Else
    getStr = getStr
    Exit For
    End If
    Next
    Index = Index1 + 1
    getStr = getStr & "; "
    If ExtractEmailFun = "" Then
    ExtractEmailFun = getStr
    Else
    ExtractEmailFun = ExtractEmailFun & Chr(10) & getStr
    End If
    Else
    Exit Do
    End If
    Loop
    End Function
  • To post as a guest, your comment is unpublished.
    The Chad · 4 years ago
    Hello all, I also was looking for a way to separate out the e-mail addresses, so I could put it into Outlook. I've added a "; " separator between the e-mail addresses so they don't run on together. Let me know what you think. Thanks to the author of the original for getting this together!


    Function ExtractEmailFun(extractStr As String) As String
    'Update 20150723
    Dim CharList As String
    On Error Resume Next
    CheckStr = "[A-Za-z0-9._-]"
    ExtractEmailFun = ""
    Index = 1
    Do While True
    Index1 = VBA.InStr(Index, extractStr, "@")
    getStr = ""
    If Index1 > 0 Then
    For p = Index1 - 1 To 1 Step -1
    If Mid(extractStr, p, 1) Like CheckStr Then
    getStr = Mid(extractStr, p, 1) & getStr
    Else
    Exit For
    End If
    Next
    getStr = getStr & "@"
    For p = Index1 + 1 To Len(extractStr)
    If Mid(extractStr, p, 1) Like CheckStr Then
    getStr = getStr & Mid(extractStr, p, 1)
    Else
    getStr = getStr
    Exit For
    End If
    Next
    Index = Index1 + 1
    getStr = getStr & "; "
    If ExtractEmailFun = "" Then
    ExtractEmailFun = getStr
    Else
    ExtractEmailFun = ExtractEmailFun & Chr(10) & getStr
    End If
    Else
    Exit Do
    End If
    Loop
    End Function
  • To post as a guest, your comment is unpublished.
    loni · 4 years ago
    This is great, but now I have a problem. There is a space instead of a period between all the email addresses (i.e. abcd@aol com), so the formula is not putting the end of all the email addresses (.net, .com, etc.) into the new column. How can I fix this?
  • To post as a guest, your comment is unpublished.
    Ulli · 5 years ago
    Thank you so much, this is very handy!
  • To post as a guest, your comment is unpublished.
    Nikita · 5 years ago
    Amazing! Big regards for VBS script! THX!
  • To post as a guest, your comment is unpublished.
    ME · 5 years ago
    no VBA just formula to be pasted into cell

    Just change the references ( the example below looks at Cell A1)

    =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))
    • To post as a guest, your comment is unpublished.
      Ahsan Hassan · 3 years ago
      [quote name="ME"]no VBA just formula to be pasted into cell

      Just change the references ( the example below looks at Cell A1)

      =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))[/quote]


      Thank you. It worked for me.
    • To post as a guest, your comment is unpublished.
      Denis de Castro BMCP · 5 years ago
      [quote name="ME"]...

      =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))[/quote]

      Thanks "ME", works verbatim in Google spreadsheet!
  • To post as a guest, your comment is unpublished.
    Neal · 5 years ago
    Great code! Really useful. Unfortunately it's concatenating multiple email addresses rather than separating them usefully, for example with a semicolon. How would you add in a separator?
  • To post as a guest, your comment is unpublished.
    Justin · 5 years ago
    Thanks for this formula! You just saved me a ton of work - had to extract 1500 emails from a poorly written Excel sheet for an email marketing list. Once I finally found your formula it was a snap.
  • To post as a guest, your comment is unpublished.
    penn · 5 years ago
    very helpful, thanks!
  • To post as a guest, your comment is unpublished.
    manas · 5 years ago
    LUL USE THIS CODE FOR EMAIL EXTRACT.

    Sub lula()
    Dim d1 As Variant
    cntr = 0
    rowstring = ActiveCell.Offset(0, 0).Value

    d1 = Split(rowstring, " ")


    Do

    'MsgBox d1(cntr)
    cntr = cntr + 1
    If d1(cntr) = "" Then GoTo ttt
    If InStr(d1(cntr), "@") Then
    MsgBox d1(cntr)
    GoTo ttt
    End If

    Loop While d1(cntr) ""


    ttt:
    End Sub