formula or code to extract hyperlink from displayed text
 

Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?
--
CT3or4

formula or code to extract hyperlink from displayed text
 

As long as your cells to be linked are in one column (the code can be
modified if not), then this should work if the links are in column E.

Sub ToAssignLinks()
For Each cell In Range("E1:E" & Range("E65536").End(xlUp).Row)
cell.Activate
If Left(ActiveCell, 7) = "http://" Then ActiveCell.Hyperlinks.Add
Anchor:=Selection, Address:= _
ActiveCell.Value, TextToDisplay:=cell.Value

Next cell
End Sub



Sub ToRemoveLinks()

With Range("E1:E" & Range("E65536").End(xlUp).Row)
.Hyperlinks.Delete
End With



HTH,
Paul


End Sub
"CT3or4" wrote in message
...
Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order
to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?
--
CT3or4

formula or code to extract hyperlink from displayed text
 

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function
--
Gary''s Student - gsnu200718


"CT3or4" wrote:

Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?
--
CT3or4

formula or code to extract hyperlink from displayed text
 

Code from Ron de Bruin

Hyperlink in column A and address returned to Column B

Sub Test()
Dim hlnk As Hyperlink
For Each hlnk In Columns("A").Hyperlinks
hlnk.Parent.Offset(0, 1).Value = hlnk.Address
Next
End Sub


Gord Dibben MS Excel MVP


On Tue, 1 May 2007 12:28:00 -0700, CT3or4
wrote:

Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?

formula or code to extract hyperlink from displayed text
 

Excellent!! Dropped it in, ID'd the columns and it ran like a derby winner.
Tried for a year with field-sales parsing so I hate that it's so short and
clean but, then, I can breathe again. Awesome.

Got 2 other replies but clicked the bottom one first,; gotta try them, too,
for grins. Efficiency will be tough to beat.
Thanks!
--
CT3or4


"Gord Dibben" wrote:

Code from Ron de Bruin

Hyperlink in column A and address returned to Column B

Sub Test()
Dim hlnk As Hyperlink
For Each hlnk In Columns("A").Hyperlinks
hlnk.Parent.Offset(0, 1).Value = hlnk.Address
Next
End Sub


Gord Dibben MS Excel MVP


On Tue, 1 May 2007 12:28:00 -0700, CT3or4
wrote:

Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?

formula or code to extract hyperlink from displayed text
 

Paul,
I get a compile error ("Arg not optional") on Hyperlinks."Add".
Then, 'Anchor', 'Address' & 'TextToDisplay' remain red-font.

Any libraries I need to install or check?
Thanks,

--
CT3or4


"PCLIVE" wrote:

As long as your cells to be linked are in one column (the code can be
modified if not), then this should work if the links are in column E.

Sub ToAssignLinks()
For Each cell In Range("E1:E" & Range("E65536").End(xlUp).Row)
cell.Activate
If Left(ActiveCell, 7) = "http://" Then ActiveCell.Hyperlinks.Add
Anchor:=Selection, Address:= _
ActiveCell.Value, TextToDisplay:=cell.Value

Next cell
End Sub



Sub ToRemoveLinks()

With Range("E1:E" & Range("E65536").End(xlUp).Row)
.Hyperlinks.Delete
End With



HTH,
Paul


End Sub
"CT3or4" wrote in message
...
Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order
to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?
--
CT3or4

formula or code to extract hyperlink from displayed text
 

GSNU,
I'm sure it's pilot error but I'm too green & occassional-user to know what
I'm doing wrong.
Without altering your code, what's the most efficient way to drop this in
and use it?
i.e. could it be changed to a sub and accessed from the macro menu?

Sorry but it's not easy being green...
--
CT3or4


"Gary''s Student" wrote:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function
--
Gary''s Student - gsnu200718


"CT3or4" wrote:

Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?
--
CT3or4

formula or code to extract hyperlink from displayed text
 

Thanks for the feedback.

Ron will be pleased his macro did the job.


Gord

On Tue, 1 May 2007 13:24:01 -0700, CT3or4
wrote:

Excellent!! Dropped it in, ID'd the columns and it ran like a derby winner.
Tried for a year with field-sales parsing so I hate that it's so short and
clean but, then, I can breathe again. Awesome.

Got 2 other replies but clicked the bottom one first,; gotta try them, too,
for grins. Efficiency will be tough to beat.
Thanks!

formula or code to extract hyperlink from displayed text
 

I think it was because of some wrap-around. The following should be on a
single line.

If Left(ActiveCell, 7) = "http://" Then ActiveCell.Hyperlinks.Add
Anchor:=Selection, Address:= _



"CT3or4" wrote in message
...
Paul,
I get a compile error ("Arg not optional") on Hyperlinks."Add".
Then, 'Anchor', 'Address' & 'TextToDisplay' remain red-font.

Any libraries I need to install or check?
Thanks,

--
CT3or4


"PCLIVE" wrote:

As long as your cells to be linked are in one column (the code can be
modified if not), then this should work if the links are in column E.

Sub ToAssignLinks()
For Each cell In Range("E1:E" & Range("E65536").End(xlUp).Row)
cell.Activate
If Left(ActiveCell, 7) = "http://" Then ActiveCell.Hyperlinks.Add
Anchor:=Selection, Address:= _
ActiveCell.Value, TextToDisplay:=cell.Value

Next cell
End Sub



Sub ToRemoveLinks()

With Range("E1:E" & Range("E65536").End(xlUp).Row)
.Hyperlinks.Delete
End With



HTH,
Paul


End Sub
"CT3or4" wrote in message
...
Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in
order
to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using
the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?
--
CT3or4

formula or code to extract hyperlink from displayed text
 

Simple and elegant. What a time saver. I used this to extract from over 300
links I needed to pull. Thank you, thank you!

"Gord Dibben" wrote:

Code from Ron de Bruin

Hyperlink in column A and address returned to Column B

Sub Test()
Dim hlnk As Hyperlink
For Each hlnk In Columns("A").Hyperlinks
hlnk.Parent.Offset(0, 1).Value = hlnk.Address
Next
End Sub


Gord Dibben MS Excel MVP


On Tue, 1 May 2007 12:28:00 -0700, CT3or4
wrote:

Huge Excel files (& files) with 'text dislayed' (long descriptions) as
hyperlink that need to be converted to URL (http://etc) hyperlink in order to
save/use in various apps & dbs.
How can I/we convert or extract the URL in in the cell without using the
"Edit Hyperlink" tool one-cell-at-a-time to cut, paste?