formula or code to extract hyperlink from displayed text

C

CT3or4

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

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
 
G

Gary''s Student

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
 
G

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
 
C

CT3or4

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!
 
C

CT3or4

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,
 
C

CT3or4

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

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

Norm (I know nothing)

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