We help IT Professionals succeed at work.

Get url from hyperlink column cells

I have an excel worksheet with two columns:

Site          url
--------      ------
Site1       ...
Site2       ...

Question: How can I get url text from hyperlinked Site column and store it as text in url column for each row?
Watch Question


One way could be with VBA

You could create a Module with below code:
Function GetURL(cell As Range, _
Optional default_value As Variant)

If (cell.Range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
GetURL = cell.Range("A1").Hyperlinks(1).Address & "#" & cell.Range("A1").Hyperlinks(1).SubAddress
End If
End Function

Open in new window

Then, If you have an Hyperlink in A1, in B1 you can add the formula:

Open in new window

This will give you in Text your link address.

If you have multiple Hyperlinks in your column A, just past the formula in each cells in column B

See attachment as an example.
Mike EghtebasDatabase and Application Developer


How can I loop through all the rows in the active worksheet to do this?
If (cell.Range("D1").Hyperlinks.Count <> 1) Then
cell.Range("D1").Text = cell.Range("D1").Hyperlinks(1).Address & "#" & cell.Range("D1").Hyperlinks(1).SubAddress
End If

Open in new window

Actually, you don't need to change anything in the module.

Just by pasting the formula in the cells will automatically update the URL text.
get urlGet-URL-2.xlsm
Are you able to test like this?

Function GetURL(rang As Range) As String
  If rang(1).Hyperlinks.Count Then GetURL = rang.Hyperlinks(1).Address
End Function

Open in new window

Then, the formula will again be for example: =GetURL(D1)

To your question, yes, it is only a function call.
You may want to also try my last function. shorter and still work great. :)