Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
BigIan
 
Posts: n/a
Default Can you extract URL values from a list of cells?

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?

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)

--
HTH

Bob Phillips

"BigIan" wrote in message
...
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?



  #3   Report Post  
BigIan
 
Posts: n/a
Default

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


"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

use like

=URL(A1)

--
HTH

Bob Phillips

"BigIan" wrote in message
...
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?




  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.


BigIan wrote:

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

"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

use like

=URL(A1)

--
HTH

Bob Phillips

"BigIan" wrote in message
...
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?





--

Dave Peterson
  #5   Report Post  
BigIan
 
Posts: n/a
Default

Dave,

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

Ian

"Dave Peterson" wrote:

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.


BigIan wrote:

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

"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

use like

=URL(A1)

--
HTH

Bob Phillips

"BigIan" wrote in message
...
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?





--

Dave Peterson



  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
AndreaSykes
 
Posts: n/a
Default Can you extract URL values from a list of cells?

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

"Dave Peterson" wrote:

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.


BigIan wrote:

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

"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

use like

=URL(A1)

--
HTH

Bob Phillips

"BigIan" wrote in message
...
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?





--

Dave Peterson

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Can you extract URL values from a list of cells?

"AndreaSykes" wrote:
.. 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 !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sum up values only in cells that are color filled? TryingExcel Excel Worksheet Functions 7 April 12th 09 03:34 PM
AutoFilter list of values R.J.H. Excel Discussion (Misc queries) 2 April 19th 05 04:49 PM
Function to list values of last 3 non-blank cells in a vertical bl ANJ Excel Worksheet Functions 1 April 14th 05 12:53 AM
making used values fall from a list static69 Excel Discussion (Misc queries) 4 February 17th 05 03:04 AM
extract data from cells Nicole L. Excel Worksheet Functions 2 February 2nd 05 09:54 PM


All times are GMT +1. The time now is 02:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"