I have a column of text which are all hyperlinks. I want to copy the text and url into seperate fields.

For Example Cell(A1) has "Apple" (hyperlink to http://www.apple.com)

I want to put the values into D and E

D1 would contain "Apple"

E1 would contain the URL http://www.apple.com

Function GetURL(HyperlinkCell As Range)
    GetURL = Replace(HyperlinkCell.Hyperlinks(1).Address, "file:", "")
End Function

I tried this in ThisWorkbook (VBA) and I got the results as #NAME? in my spreadsheet


You need to place your function in a standard module:

enter image description here

and then it will work:

enter image description here


You have to place your code in a normal module:

  • Right click your project in the VBE
  • Insert: Module
  • (Optionally) give your module a more sensible name

Cut/Paste your code there. It should work just fine then!

To make your intentions a bit more clear, you can extend your function header by explicitly declaring it as a Public function (not stating it will not change anything, but some people then assume it to be implicitly Private) and by setting the return type to String:

Public Function GetURL(HyperlinkCell As Range) as String

As the Hyperlink property only contains strings, there's nothing wrong with making it explicit in your UDF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.