excel linked text - how to get the url of the link?

supergirl2008
supergirl2008 used Ask the Experts™
on
look at the excel file and see col. B
it has linked text so you see a filename but when you hover over the cell or click a cell it redirects you to a URL.

how can i extract the url's of these cell's linked text into another col?
FormData-1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
Hi

The easiest way is to use a VBA UDF to return the address of the hyperlink - this would mean you will need to save your file as an .xlsm file should you wish to keep the links dynamic (ie update if you change the address).  One such function possibility is this:

and I have applied it to your file (attached).

Best regards

Richard


Function ReturnURL(r As Range) As String
If r.Hyperlinks.Count > 0 Then _
    ReturnURL = r.Hyperlinks(1).Address
End Function

Open in new window

FormData-1.xlsm
something like this with a UDF

from http://www.techonthenet.com/excel/macros/extract_hl.php

Cheers

Dave

Function HyperLinkText(pRange As Range) As String
    Application.Volatile

        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

Open in new window

testhyp.xls
.. too slow on attachments and code posting

All points to Richard pls

Dave
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
richard,
how can i add this to my files if i start out with a normal xslx file?
1. where do i add the code for the ReturnURL function in excel?

thanks the file from richard is perefect. just trying to learn it myself now.

Author

Commented:
brent your fails and just sais #NAME? in all the fields you try to calculate the url in
- Press Alt & F11 together to go to the VBE
- Insert Module
- copy and paste in Richard's code
- Alt & F11 back to "normal" Excel

Dave
.... the file I attached worked fine. Did you open that?

Dave
Top Expert 2009
Commented:
Assuming you have your .xlsx file open and visible in Excel, open up the VBE with Alt+F11.  In the top left of the VBE is the Project Window, and you should see your file listed here.  If you right-click on it, you should have an option to Insert>Module.

This will insert a standard module into the project which will now be visible on the right-hand side of the VBE (as a white window) - this is to where you need to copy and paste the code I gave you.  Once the code is pasted into this window, you can use the function as you would any other formula in Excel eg select a cell and type:

=ReturnURL(A1)  

Just to reiterate, you will need to save the file down as an xlsm if you don't want to copy and paste values over the UDF formulas (as .xlsx files don't support VBA code).

Richard

Author

Commented:
perfect, thanks

Author

Commented:
im getting my cells tellink me #NAME

what am i missing?
Top Expert 2009

Commented:
Can you attach an example workbook where this is happening?  The workbook will need to contain a module with the code in it for the formula to work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial