Extracting a email address for a macro

Tim Chambers

New Member
Joined
Mar 3, 2005
Messages
3
I have a rather extensive data base that I'd like to get the email addresses out of the persons name and actually display

i.e. Cell A1 has 'John Doe' as the data but if you go over it you get 'mailto:JohnDoe@TheInternet.com'

I want to extract his email address from A1 and place it in cell B1

When recording the macro I right clicked/hyperlink/copy hyperlink/paste
It worked great...however when I run the macro B1 just says John Doe with the the hyperlink if you go over the cell; just as the original cell A1

Thanks
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Try this code.
Code:
Sub GetEmail()
Dim LastRow As Long
Dim I As Long
Dim rng As Range

    LastRow = Range("A65536").End(xlUp).Row
    
    For I = 2 To LastRow
    
        Set rng = Range("A" & I)
        
        rng.Offset(0, 1) = Mid(rng.Hyperlinks(1).Address, 8)
    Next I
End Sub
 

Tim Chambers

New Member
Joined
Mar 3, 2005
Messages
3
had to add an on error statement for those without emails, but other than that is worked great!!!


Thanks
:biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,414
Messages
5,596,001
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top