To extract hyperlink from a cell

gatomouce

New Member
Joined
Aug 10, 2018
Messages
2
Hello !

I am trying to extract the hyperlink from a lot of cells.

I know we can extract manually the URL by editing the link... but this could be done only one at a time (I think :)) and I have hundreds to work on

I found few visual basic codes to extract the link... like this one:

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

I tried other ones but the result is always the same: the link is not copied completely.

This hyperlink is an example: https://fmsqprod.myabsorb.ca/#/online-courses/2a985219
After I applied the formula (=extracURL), all that I get is https://fmsqprod.myabsorb.ca/

Does someone knows how I can get the complete URL ?

Thank you in advance !
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi @gatomouce, Welcome to the MrExcel Message Board!

In this case you're in the need of the SubAddress property as well. For a usage example see the attached minisheet.

VBA Code:
Public Function UrlInfo(argTarget As Range, argProperty As String) As String
    Application.Volatile
    On Error Resume Next
    Select Case LCase(argProperty)
        Case "address"
            UrlInfo = argTarget.Hyperlinks(1).Address
        Case "emailsubject"
            UrlInfo = argTarget.Hyperlinks(1).EmailSubject
        Case "name"
            UrlInfo = argTarget.Hyperlinks(1).Name
        Case "screentip"
            UrlInfo = argTarget.Hyperlinks(1).ScreenTip
        Case "subaddress"
            UrlInfo = argTarget.Hyperlinks(1).SubAddress
        Case "texttodisplay"
            UrlInfo = argTarget.Hyperlinks(1).TextToDisplay
        Case Else
            UrlInfo = "#N/A"
    End Select
End Function

Cell Formulas
RangeFormula
B2:B5B2=UrlInfo($A2,"Name")
C2:C5C2=UrlInfo($A2,"Address")
D2:D5D2=UrlInfo($A2,"SubAddress")
E2:E5E2=UrlInfo($A2,"ScreenTip")
F2:F5F2=UrlInfo($A2,"TextToDisplay")
 
Upvote 0
Solution
Thank you very, very much @GWteB !!! This fixed my problem and I can now go forward in this project... you relieved me of immense stress !

Have a nice day !
 
Upvote 0
You are welcome and thanks for letting me know.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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