How to extract email address in hyperlink
 

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

How to extract email address in hyperlink
 

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


--
Gary's Student
gsnu200702


"Brossyg" wrote:

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

How to extract email address in hyperlink
 

Check your other post, too.

Brossyg wrote:

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

--

Dave Peterson

How to extract email address in hyperlink
 

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?

"Gary''s Student" wrote:

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


--
Gary's Student
gsnu200702


"Brossyg" wrote:

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

How to extract email address in hyperlink
 

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.

Brossyg wrote:

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?

"Gary''s Student" wrote:

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


--
Gary's Student
gsnu200702


"Brossyg" wrote:

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

--

Dave Peterson

How to extract email address in hyperlink
 

Works for me in a standard module, not worksheet code.
--
Gary''s Student
gsnu200702


"Brossyg" wrote:

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?

"Gary''s Student" wrote:

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


--
Gary's Student
gsnu200702


"Brossyg" wrote:

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

How to extract email address in hyperlink
 

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

Gary''s Student wrote:

Works for me in a standard module, not worksheet code.
--
Gary''s Student
gsnu200702

"Brossyg" wrote:

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?

"Gary''s Student" wrote:

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


--
Gary's Student
gsnu200702


"Brossyg" wrote:

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

--

Dave Peterson