Can you extract URL values from a list of cells?

[Guest]

I have a list of cells that are formatted as hyperlinks. Is there a way to
extract the URL value from each cell and create an adjacent column with these
values in it?

 

[Bob Phillips]

Here is a simple UDF you can use

Function URL(rng As Range)
If rng.Cells.Count > 1 Then
URL = CVErr(xlErrRef)
Else
URL = rng.Hyperlinks(1).Address
End If
End Function

use like

=URL(A1)

 

[Guest]

Hi Bob,

Thanks for your reply but it's a bit advanced for me, I don't know what a
UDF is and can't find it in Excel help. I can use basic functions inside
cells and I've done some macros but that's about my limit.
Does it run like a macro and if so how do I save it and run it?

Thanks,
Ian

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=URL(A1)
Where A1 contained the hyperlink.

 

[Guest]

Dave,

Thanks for the crash course, superb!
Got it now, very grateful to you both.

Ian

 

[Harlan Grove]

Bob Phillips wrote...

Here is a simple UDF you can use

Function URL(rng As Range)
If rng.Cells.Count > 1 Then
URL = CVErr(xlErrRef)
Else
URL = rng.Hyperlinks(1).Address
End If
End Function

....

Perhaps too simple. As long as the range argument spans a single area
and fewer than 5,000-odd rows, why not return an array?


Function url(r As Range) As Variant
Dim rv As Variant, i As Long, j As Long

If r.Areas.Count = 1 And r.Areas(1).Cells.Count < 5100 Then
rv = r.Value

For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
rv(i, j) = IIf(r.Cells(i, j).Hyperlinks.Count > 0, _
r.Cells(i, j).Hyperlinks(1).Address, "")
Next j
Next i

Else
rv = CVErr(xlErrRef)

End If

If r.Cells.Count = 1 Then rv = rv(1, 1)
End Function

 

[Guest]

This was VERY helpful for me as well. It worked great. Thanks to Microsoft
for access to this community.

 

[Max]

.. Thanks to Microsoft for access to this community.

Believe it's also the many wonderful responder folks (like Dave Peterson,
Bob Phillips, David McRitchie, et al) who deserve many, many thanks for
their incessant pumping out of gems to posts ..
Merry Christmas to all !