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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,803
Office Version
  1. 2013
Platform
  1. Windows
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")
 
Solution

gatomouce

New Member
Joined
Aug 10, 2018
Messages
2
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 !
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,803
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know.
 

Forum statistics

Threads
1,141,062
Messages
5,704,061
Members
421,325
Latest member
tapete86

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
Top