I need to copy a column that has linked text and paste a column that shows all the URL’s for the linked text
1
-
Can you clarify please? Do you mean that you have a column with Hyperlinks (only hyperlinks? Or a mix of links and other contents?) and that you want to extract all the urls of these links? Do they have have to be on the same row as the original?Mathias– Mathias2010-07-03 03:33:08 +00:00Commented Jul 3, 2010 at 3:33
Add a comment
|
3 Answers
Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function
In this case you can place it where you want. If you want, for example, the URL from a hyperlink in A1 to be listed in cell C25, then in cell C25 you would enter the following formula:
=GetURL(A1)
2 Comments
Marcus Lucero
=GetURL(A1) function does not work in Excel 2013. At least not from my version of 2013
Peter Elias
You can add the code inside a module, and try to use as above, it works too on excel 2013
This immediately does the job, and add a separate url link column next to the column with the hyperlinked text:
https://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel
Extracting a URL from a hyperlink on Excel -- this worked for me! If you want to run this operation one time Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function below (customized function):
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
Press F5 and click “Run”
Get out of VBA (Press Alt+Q)
You will see a new column with a list of url added to the right.
