I'm storing this info here so I can find it later. I have a spreadsheet with a lot of URLs stored in a column (more than 150). The URLs are embedded in files stored on a remote server so when you click on the file it will take you to the file.
Great, except I need the actual hyperlink info. I could get the URL by right clicking then cutting/pasting each file, but that would take hours. So I need a custom function or macro to extract them for me.
The basic info is copied from this location:
https://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html
The cure for tedium—like them or not—is a macro. With a macro, getting at the underlying URL for a hyperlink is child's play. All the macro needs to do is pay attention to the Address property of the hyperlink. The following is an example of a macro that will find each hyperlink in a worksheet, extract each one's URL, and stick that URL in the cell directly to the right of the hyperlink.
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
Instead of a "brute force" macro, you could also create a user-defined function that would extract and return the URL for any hyperlink at which it was pointed:
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)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3281) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here:
Extracting URLs from
How to create a custom function
That's all good and fun, but it doesn't tell me how to create a custom function. So, here's another source:
http://www.dummies.com/software/microsoft-office/excel/how-to-create-custom-excel-functions/
BEFORE YOU PROCEED, you will need to go into your Excel settings and, in the File > Options you need to edit the Auto correct setting that automatically turns URLs into hyperlinks.
Press Alt + F11.
This gets you to the Visual Basic Editor, where VBA is written.
You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box.
Choose Insert→Module in the editor.
You have an empty code module sitting in front of you. Now it’s time to create your very own function!
Type this programming code, shown in the following figure:
Writing your own function.
Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function
Macros and VBA programming can be saved only in a macro-enabled workbook.
After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook. Save the file as a Macro-Enabled Excel file (necessary).
Return to Excel.
Click the Insert Function button on the Formulas tab to display the Insert Function dialog box.
Finding the function in the User Defined category.
Click OK.
The Function Arguments dialog box opens, ready to receive the arguments. Isn’t this incredible? It’s as though you are creating an extension to Excel, and in essence, you are.
Using the custom Add function.
BTW: I am not copying this here to rip off anybody's content. I'm just storing it here in case the original links ever go away.
