formula or code to extract hyperlink from displayed text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CT3or4 - 01 May 2007 20:28 GMT

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?

Signature

CT3or4

Reply to this Message

PCLIVE - 01 May 2007 20:46 GMT

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

> 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?

Reply to this Message

CT3or4 - 01 May 2007 21:53 GMT

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,

Signature

CT3or4

> 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.
[quoted text clipped - 25 lines]
> > 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?

Reply to this Message

PCLIVE - 02 May 2007 15:50 GMT

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:= _

> Paul,
> I get a compile error ("Arg not optional") on Hyperlinks."Add".
[quoted text clipped - 34 lines]
>> > the
>> > "Edit Hyperlink" tool one-cell-at-a-time to cut, paste?

Reply to this Message

Gary''s Student - 01 May 2007 20:49 GMT

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

Signature

Gary''s Student - gsnu200718

> 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?

Reply to this Message

CT3or4 - 01 May 2007 22:14 GMT

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...

Signature

CT3or4

> Function hyp(r As Range) As String
> hyp = ""
[quoted text clipped - 17 lines]
> > 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?

Reply to this Message

Gord Dibben - 01 May 2007 20:55 GMT

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

>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?

Reply to this Message

CT3or4 - 01 May 2007 21:24 GMT

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!

Signature

CT3or4

> Code from Ron de Bruin
>
[quoted text clipped - 14 lines]
> >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?

Reply to this Message

Gord Dibben - 01 May 2007 22:16 GMT

Thanks for the feedback.

Ron will be pleased his macro did the job.

Gord

>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
[quoted text clipped - 3 lines]
>for grins. Efficiency will be tough to beat.
> Thanks!

Reply to this Message

Norm ("I know nothing") - 17 May 2007 22:51 GMT

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

> Code from Ron de Bruin
>
[quoted text clipped - 14 lines]
> >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?

Reply to this Message