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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.