Excel has no built-in function for this, so add a short UDF. Press Alt + F11, choose Insert → Module and paste:
<pre>Function GetURL(c As Range) As String</pre>
If c.Hyperlinks.Count > 0 Then
GetURL = c.Hyperlinks(1).Address
If c.Hyperlinks(1).SubAddress <> "" Then
GetURL = GetURL & "#" & c.Hyperlinks(1).SubAddress
End If
End If
End Function
Then type =GetURL(A2) in the sheet and fill down. Save the file as .xlsm so the function stays with it.
Links to a place in the same workbook keep their target in SubAddress, not Address, which is why the function adds it after a #. Without that step, internal links come back blank.
Links made with the HYPERLINK formula aren’t hyperlink objects, so the function returns blank for them. In Excel 365, =TEXTBEFORE(TEXTAFTER(FORMULATEXT(A2),""""),"""") pulls the address out of the formula when it’s typed in quotes.
For just a few cells, right-click the cell, choose Edit Hyperlink and copy the URL from the Address box.
Let’s say you got an Excel workbook from your colleague where you have a list of hyperlinks in a column. Now from this list, you want to extract all the hyperlink URL addresses.
The manual method is a real pain you need to copy-paste each URL one by one. So the better way is to create a UDF in VBA. And, today I’d like to share with you a simple code for this UDF.
VBA code to Extract Hyperlink URLs
Function GiveMeURL(rng As Range) As String
On Error Resume Next
GiveMeURL = rng.Hyperlinks(1).Address
End FunctionHow to use this Code
To use this UDF, you need to follow these steps:
- Open the visual basic editor from the developer tab or use the keyboard shortcut ALT + F11.
- Insert a new module, paste the above code into it and close it.
- Now in your worksheet, enter function =GiveMeURL(A1) and hit enter.
It will return the URL from the cell where you have a hyperlink and make sure to download this file from here.
Could you please explain these hyperlink VBA WITH SOME EXAMPLES OF EXTRACTION.
Hi, I have a column E that contains Hyperlinks from row 2 to 25, or mabye just in row 3.
Im supposed to check wich rows that contains a hyperlink and then get the links over to outlook mai.
I’ve used A for 2 to 25, checking if cell is empty, but don quite get the link over from that cell containing hyperlink and into mail. How can I do that?
Steinar