Retrieving a hyperlink

[ma_nor]

Hi All,
Through a formula I am retrieving some informaition from another workbook. That info includes hyperlinks but the formula only retrieves the text but not the hyperlink. Does anyone knows how to retireve the text with the hyperlink?
Thank you

 

[GWteB]

Hi ma_nor, welcome to the board.
As far as I know this can only be done with an user defined function in VBA. Whenever you are not familiar: open the VB editor with ALT + F11, click in the menu on Insert > Module. In the pane that appears you can paste the code below. Note that you have to save your workbook as an Excel Macro-Enabled Workbook ( .xlsm).
You can use this function in formulas on your worksheet, see attached examples.

Public Function UrlInfo(argTarget As Range, argProperty As String) As String
    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

Book1
	A	B
1	Open Forms	D:\TEMP\Book2.xlsx
2		Scortcut to Book2
3	Retrieving a hyperlink	https://www.mrexcel.com/board/threads/retrieving-a-hyperlink.1128148/
4		MrExcel
5	John Doe	mailto:john.doe@forever.com?subject=Upcoming%20meeting
6	first order	Orders!H6
Sheet1

Cell Formulas
Range	Formula
B1	B1	=UrlInfo(A$1,"Address")
B2	B2	=UrlInfo(A$1,"ScreenTip")
B3	B3	=UrlInfo(A$3,"Address")
B4	B4	=UrlInfo(A$3,"ScreenTip")
B5	B5	=UrlInfo(A$5,"Address")
B6	B6	=UrlInfo(A$6,"SubAddress")