0

Say I have a sheet that lists several names and hyperlinks:

NAME      AGE
Trout      21
Gomez      28
McCutchen  26

Each NAME is a hyperlink to a website. Is there a way to copy all of these hyperlinks to a new column that says something standard like Click Here? I would then strip the hyperlinks from the names themselves:

NAME     AGE     LINK
Trout     21      Here
Gomez     28      Here
McCutchen 26      Here

Basically I want clean names but retain the link in a separate column. Alternatively, I can copy the linked names, and make some kind of bulk change to the text? Not sure how to do it, I have lots of names...

NOTE: I tried the HYPERLINK() formula but it wouldn't open the webpage, whereas the names do.

Jeffrey Kramer
  • 1,345
  • 6
  • 25
  • 43
  • [Possible duplicate](http://stackoverflow.com/questions/5646549/extracting-a-url-from-hyperlinked-text-in-excel-cell) – kei Jan 10 '14 at 18:47

1 Answers1

0

Using some of the info found in the link that kei posted, I came up with this:

    Sub extract_links()
        Range("c1").Value = "link"
        r = 2
        Do While Not IsEmpty(Range("a" & r))
            Range("c" & r).Value = Range("a" & r).Hyperlinks(1).Address
            r = r + 1
        Loop
        Columns("c:c").EntireColumn.AutoFit
    End Sub

I hope it helps~

chrono
  • 138
  • 7