If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extracting hyperlinks
Hi,
I have got an excel spreadsheet which has 500+ hyperlinks. I need to extract the address behind the text to be able to import the actual address into a db. e.g cell A1 has "click here" with http:/www.xyz.com behind it. I need to be able to copy the cell "click here" and paste it (special?) so that what i am pasting is http:/www.xyz.com. Paste value doesn't seem to work nor formula. Cheers |
#2
|
|||
|
|||
Extracting hyperlinks
Hi there
Assuming that the hyperlinks are in the same column (starting in A1) the following code will place the address of the hyperlink in the cell directly to the right of the hyperlink. Sub GetHyperlinkAddress() 'Code snippet courtesy of Paul Falla' Dim HyperlinkAddress As String On Error Resume Next Range("A1").Activate While Not IsEmpty(ActiveCell) HyperlinkAddress = ActiveCell.Hyperlinks(1).Address ActiveCell(, 2) = HyperlinkAddress ActiveCell.Offset(1, 0).Select Wend End Sub It may be an idea to cut and paste the thyperlinks on to a new worksheet to ensure that your original data remains intact. Hope this helps Paul Falla -----Original Message----- Hi, I have got an excel spreadsheet which has 500+ hyperlinks. I need to extract the address behind the text to be able to import the actual address into a db. e.g cell A1 has "click here" with http:/www.xyz.com behind it. I need to be able to copy the cell "click here" and paste it (special?) so that what i am pasting is http:/www.xyz.com. Paste value doesn't seem to work nor formula. Cheers . |
#3
|
|||
|
|||
Extracting hyperlinks
Another way (quite similar code) is to use a function that you can use in a cell
in the worksheet. Saved from a previous post: One way to extract those URL's from a hyperlink is with a userdefinedfunction. Here's one that may help: Option Explicit Function GetURL(Rng As Range) As String Application.Volatile Set Rng = Rng(1) If Rng.Hyperlinks.Count = 0 Then GetURL = "" Else GetURL = Rng.Hyperlinks(1).Address End If End Function So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent cell. Then convert those formulas to values. Then use =hyperlink(b1) instead. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm And after you convert those formulas to values (edit|copy, edit|paste special|values), you can delete the links from the other cells. Select the cells and run this macro: sub deletelinks() selection.hyperlinks.delete end sub wrote: Hi, I have got an excel spreadsheet which has 500+ hyperlinks. I need to extract the address behind the text to be able to import the actual address into a db. e.g cell A1 has "click here" with http:/www.xyz.com behind it. I need to be able to copy the cell "click here" and paste it (special?) so that what i am pasting is http:/www.xyz.com. Paste value doesn't seem to work nor formula. Cheers -- Dave Peterson |
#4
|
|||
|
|||
Extracting hyperlinks
worked beautifully. THANKS!
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
mailto: hyperlinks in Excel spreadsheet have flipped out!! | causemaker | Links and Linking | 1 | April 13th, 2004 12:53 AM |
How to make Excel 2002 to automatically update worksheet names in hyperlinks? | Dmitriy Kopnichev | Links and Linking | 2 | March 23rd, 2004 11:23 PM |
What problems can I expect with 1000 hyperlinks? | Peter Noneley | Links and Linking | 0 | November 25th, 2003 11:04 AM |
Sorting with Internet Hyperlinks | RYoung | Worksheet Functions | 0 | September 24th, 2003 07:19 PM |