How to extract email address in hyperlink

[Guest]

I copied several hundred email address hyperlinks from an html page into a
spreadsheet. They all showed text as "Click her to email" on the html page.
They copied correctly as hyperlink "mailto" links, but the text in the excel
field is still "Click her to email". The email links span A1 - A500. I am
trying to find a way to show the email address only in B1 - B500. How do i
do this?

Brossyg

 

[Guest]

If the hyperlink is in A1, then
=hyp(A1) will return the linkage part. Here is the VBA

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

If you are unfamiliar with VBA, See:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

Check your other post, too.

I copied several hundred email address hyperlinks from an html page into a
spreadsheet. They all showed text as "Click her to email" on the html page.
They copied correctly as hyperlink "mailto" links, but the text in the excel
field is still "Click her to email". The email links span A1 - A500. I am
trying to find a way to show the email address only in B1 - B500. How do i
do this?

Brossyg

 

[Guest]

I copied this VB code to the sheet's VB editor and then put =hyp(a1) in the
B1 cell. I get back this error: #NAME?

What should I do?

 

[Dave Peterson]

Check your other post.

It becomes a pain for you when you multipost. Now you have two threads to
check. And you essentially wasted Gary's Student's time or mine.

 

[Guest]

Works for me in a standard module, not worksheet code.

 

[Dave Peterson]

The OP had to check multiple threads. And he posted back in the other thread
that he got it working.