Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract email address (Excel 2003)

    I've got a bunch of web pages in HTML format that have website links
    and email addresses. Now, I can import these nicely into excel and
    having got rid of all the rubbish (i.e. headings, images that aren't
    there etc) I'm left with quite a nice table. However, I can't seem to
    be able to get to the underlying code.

    fer'instance, I could have columns like this:
    <table border=1><td>Website</td><td>Name </td><td> email</td><td>www.pootynet.com</td><td>Pootynet site </td><td>Fred Bloggs</td></table>

    And if I click on the website or the email, it loads IE or Outlook.
    But what I really want to do is get at the link. I was thinking of
    some formula that might give me:

    <table border=1><td>Website</td><td>Name </td><td> email</td><td>www.pootynet.com</td><td>Pootynet site </td><td>Fred@pootynet.com </td></table>

    In plain text. C'est possible?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract email address (Excel 2003)

    You shuld be able to get it in VB from the "hyperlinks.address" You will probably have to remove the "mailto:"
    something like
    range("C1").value = range("C1").Hyperlinks(1).Address

    I don't have enough details to provide more details to you
    Steve

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract email address (Excel 2003)

    Hi Sarge & welcome to the Lounge

    You can use a user defined function to extract an e-mail address, the result will be plain text.

    <code>Function emailAddress(cell)</code>
    <code> On Error Resume Next</code>
    <code> emailAddress = Replace(cell.Hyperlinks(1).Address, "mailto:", "")</code>
    <code> If emailAddress = 0 Then emailAddress = ""</code>
    <code>End Function</code>
    <code></code>

    As an alternative you can use this procedure. It looks in cells C1:C10 and places the e-mail addresses in cells D110

    <code>Sub GetEmailAddress()</code>
    <code> On Error Resume Next</code>
    <code> Dim r As Range, c As Range</code>
    <code> Set r = Range("C1:C10")</code>
    <code> For Each c In r</code>
    <code> c.Offset(0, 1) = Replace(c.Hyperlinks(1).Address, "mailto:", "")</code>
    <code> Next c</code>
    <code>End Sub</code>
    <code></code>

  4. #4
    New Lounger
    Join Date
    Sep 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract email address (Excel 2003)

    That, my friend, is fantastic! I owe you a hearty pint of dark beer!

  5. #5
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to email address (Excel 2003)

    I need to do the reverse.

    I have a long list of email addresses of the form name@site.suffix in column A.

    I need to convert them all to hyperlinks of form mailto:name@site.suffix. Any help appreciated!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert to email address (Excel 2003)

    If necessary, insert an empty column in column B.
    Assuming the e-mail addresses begin in cell A1, enter the following formula in B1:
    <code>
    =HYPERLINK("mailto:"&A1)
    </code>
    and fill down as far as necessary.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to email address (Excel 2003)

    Sweet!

    Thanks, Hans.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to email address (Excel 2003)

    But that results in "mailto:" in front of each email.

    If I Insert > Hyperlink > Email Address, I don't get the distracting mailto:, just an underlined hyperlink. That's what I would like.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert to email address (Excel 2003)

    Try the following macro. It acts on the column (A in this example) itself:

    Sub Convert2Email()
    Dim r As Long
    For r = 1 To Range("A65536").End(xlUp).Row
    If InStr(Range("A" & r), "@") > 0 Then
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & r), Address:="mailto:" & Range("A" & r)
    End If
    Next r
    End Sub

  10. #10
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to email address (Excel 2003)

    Perfect! Exactly what I was after.

    Thanks again, Hans

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Convert to email address (Excel 2003)

    You could also adjust Hans' original formula to:
    <code>=HYPERLINK("mailto:"&A1,A1)</code>
    and that should do it.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to email address (Excel 2003)

    Rory,

    That's also useful, for future lists.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •