One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.
Here's one that may help:
Function GetURL(Rng As Range) As String
Set Rng = Rng(1)
If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
GetURL = Rng.Hyperlinks(1).Address
So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.
If you're new to macros, you may want to read David McRitchie's intro at:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Into a test cell and type:
this will return
If you want to drop the mailto:, you could just use:
(make 255 big enough for the longest address)
I copied a table from web site to excel ehich has the email address as
hyperlink to names of people. In Excel it appears as hyperlink and shoes the
name, and email address as hyperlink value.
How can I extract the hyperlink value and have the acctual email address on
Thanks in advance