Forum Discussion

narayanareddy's avatar
narayanareddy
Copper Contributor
Jul 07, 2023
Solved

How to have Hpyer link url copied from one cell to another cell with formula

Dear all,

I have a requirement to copy excel hyperlink urls in group of cells under one column to another column of cells with a formula. The hyperlink on the cell has a display name and when click on the display name then it redirects to the link url. So, I just need to extract automatically the hyperlink url with formulas.

Please share your ideas or thoughts on how to extract hyperlink url from one cell to another cell with formula automatically. Thank you for your help and support.

 

Best Regards,

Narayana Reddy

excel
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Jul 07, 2023

    narayanareddy 

    In Excel for Windows or Mac, you can create a custom VBA function:

    • Press Alt+F11 to activate the Visual Basic Editor.
    • Select Insert > Module.
    • Copy the code listed below into the module.
    • Switch back to Excel.
    • With a hyperlink in A1, the formula =GetURL(A1) will return the URL of the hyperlink.
    • Save the workbook as a macro-enabled workbook (*.xlsm).
    • Make sure that you allow macros when you open the workbook.
    Function GetURL(cell As Range) As String
    GetURL = cell.Hyperlinks(1).Address
End Function

     

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 07, 2023

    narayanareddy 

    In Excel for Windows or Mac, you can create a custom VBA function:

    • Press Alt+F11 to activate the Visual Basic Editor.
    • Select Insert > Module.
    • Copy the code listed below into the module.
    • Switch back to Excel.
    • With a hyperlink in A1, the formula =GetURL(A1) will return the URL of the hyperlink.
    • Save the workbook as a macro-enabled workbook (*.xlsm).
    • Make sure that you allow macros when you open the workbook.
    Function GetURL(cell As Range) As String
    GetURL = cell.Hyperlinks(1).Address
End Function

     

Resources