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?
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Wilder1626Commented:
Hi

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
Else
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:
=GetURL(A1)

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
Ex:
=GetURL(A1)
=GetURL(A2)
=GetURL(A3)

See attachment as an example.
Get-URL.xlsm
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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

0
 
Wilder1626Commented:
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.
Ex:
get urlGet-URL-2.xlsm
0
 
Wilder1626Commented:
Are you able to test like this?

Module:
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.
0
 
Wilder1626Commented:
You may want to also try my last function. shorter and still work great. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.