Extracting Hyperlinks

[SAM2203]

Dear All,

I have a problem and I hope that you guys will be a great help. I have a column of hyperlinks (around 500 entries) now I want to extract hyperlinks of all entries in to a adjacent column.

What is the easiest way of doing it besides manually copying the links in to the destination cells


Thanks

 

[Scott Huish]

Since you can copy and paste the whole block at once, how is this a problem?

 

[SAM2203]

Since you can copy and paste the whole block at once, how is this a problem?

for example. I have text "yahoo" in first column and it is Hyper linked to "www.yahoo.com". I want that "yahoo" text stays in the source and and its hyperlink is copied in the destination. I mean destination text must then be "www.yahoo.com"

 

[Scott Huish]

=HYPERLINK("http://www."&A1&".com")

Copy down.

 

[SAM2203]

<table width="637" border="0" cellpadding="0" cellspacing="0"><col style="width: 478pt;" width="637"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 478pt;" width="637" height="20"> Insight Information's Chronic and Complex Health Care </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> Hands-on Workshop on Molecular Biotechnology and Bioinformatics </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> Nurses for Nurses Network Annual Conference 2011 Norfolk Island </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> WE-ASC Congress on Arts, Science & Culture </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> the 11th International Congress of Anesthesiology and Critical Care </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> CME cum Workshop on Laws applicable to hospitals: Issues, Challenges and Possible solutions</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> 3rd Annual Australian Cycling Conference


Just for the info, Above is a extract from first column. All are hyperlinked to some particular link. I want this column to stay as it is , just the hyperlinked to be pasted in destination column of my choice.
I dont think "Hyperlink" can be be used the way you have described it

Thanks
</td> </tr> </tbody></table>

 

[Leith Ross]

Hello SAM2203,

Did you import these links from a file or website? If so, you are correct that the formula =HYPERLINK will not work on this. Hyperlinks created by the formula method are not compatible with hyperlinks created by VBA or other sources.

Sincerely,
Leith Ross

 

[SAM2203]

yes they are imported from a website

 

[Leith Ross]

Hello SAM2203,

I will put together a macro that will separate the hyperlink parts for you. The links you posted show this site's URL http://www.conferencealerts.com/ for the links. Is this correct?

 

[SAM2203]

Hello SAM2203,

I will put together a macro that will separate the hyperlink parts for you. The links you posted show this site's URL http://www.conferencealerts.com/ for the links. Is this correct?

I will be obliged Sir ! yes they are from the URL you mentioned.

 

[Leith Ross]

Hello SAM2203,

Sorry for the delay, I was having some internet issues. Here is the macro. It looks in column "A" starting with row and extracts the URL from the hyperlink. The URL is place in column "B" of the same row. You can change "A1" (marked in bold) to whichever cell you want to start with. If you want to place the URL in any column other than the column to the right of the hyperlink, let me know.

Sub ExtractURL()

  Dim Cell As Range
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
    Set Rng = Wks.Range("A1")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
    
      For Each Cell In Rng
        If Cell.Hyperlinks.Count <> 0 Then
           Cell.Offset(0, 1) = Cell.Hyperlinks(1).Address
        End If
      Next Cell
    
End Sub

Sincerely,
Leith Ross