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