Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract the url in a hyperlink using a formula | Excel Worksheet Functions | |||
How do I extract hyperlink as text from an array of hyperlinks? | Excel Worksheet Functions | |||
how to extract text from a formula | Excel Discussion (Misc queries) | |||
formula is displayed as literal text instead of formula result | Excel Discussion (Misc queries) | |||
Can I extract text as a value from a formula? | Excel Worksheet Functions |