Extract URL from a link

Number1

Board Regular
Joined
May 13, 2002
Messages
83
I have copied and pasted a web page into Excel. One of the cells came in as a link but it only shows the friendly text, not the URL behind it. The URL does come up when I hover over the link but that doesn't help me.

If the link is in A1, what formula can I put in B1 to extract the URL from that link?

Can it be done without using a custom function and if so, how?

If a function is needed, can you provide the function with clear instructions on how to add it into Excel 2007 (never done this before).

Thanks for your help!!!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Easier way:
Right click on the cell--> select "Edit Hyperlink...".
In the box which comes up, the URL is in the "Address" bar.
Select and press Control+C to copy. (Right click-->Copy does not work in Excel 2003. Might work in 2007 though)
Paste wherever.
 
Upvote 0
I should have mentioned that extracting them manually via right click/edit hyperlink is not really an option becuase I have hundreds of rows that I need to extact the link from.

I should also mention that since I posted yesterday, I found some custom functions on the web that are supposed to do the link extraction but I get a #NAME? error when using the custom function. Excel seems to recognize the custom function becuase if I enter it as =geturl(ref), Excel changes it to =GetURL(ref) which is how the function name was entered.

So, I either need help with getting either of the functions below to stop giving me the #NAME? error or a different custom function that works.

BTW, I am using Excel 2007.

Thanks for your help!!!!!

--------------------------------------------
First one I tried...

Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function

--------------------------------------------
Second one I tried...

Function HyperLinkText(pRange As Range) As String
Dim ST1 As String
Dim ST2 As String
If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
End If
HyperLinkText = ST1
End Function
 
Upvote 0
Both work for me as intended.
Where have you pasted the functions?
It should me in the same workbook.
It should be on a separate code module rather than on sheet module. (You have probably pasted it in one of the sheet modules)
Your steps maybe slightly different (2007):
1. Right click on any sheet tab-->click "View Code"
2. Visual Basic Project Window-->Right click on your Workbook-->Insert-->Module.
3. Double click on the inserted module (usually named Module1 etc)
4. Paste the code in big right pane.
HTH
PS:
The second function gives a more complete address including the location in the given page.
However for a usable link use the modified code below:

Function HyperLinkText(pRange As Range) As String
Dim ST1 As String
Dim ST2 As String
If pRange.Hyperlinks.Count = 0 Then Exit Function
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
If ST2 <> "" Then ST1 = ST1 & "#" & ST2
HyperLinkText = ST1
End Function
 
Last edited:
Upvote 0
After I cleaned out the previous attempts at this code by moving my data to a new workbook, it worked. Thanks for the instructions on how to create the custom function!!!

MrExcel.com Rocks - ONCE AGAIN!!!!!!
 
Upvote 0

Forum statistics

Threads
1,223,483
Messages
6,172,532
Members
452,463
Latest member
Debz

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
Back
Top