formula or code to extract hyperlink from displayed text

[Guest]

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?

 

[PCLIVE]

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

 

[Guest]

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

 

[Gord Dibben]

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

 

[Guest]

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!

 

[Guest]

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,

 

[Guest]

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

 

[Gord Dibben]

Thanks for the feedback.

Ron will be pleased his macro did the job.


Gord

 

[PCLIVE]

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

 

[Guest]

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