Convert text to hyperlinks

I have a column of about 3000 URLs, but they're listed as text and are not actual, click-and-open-in-browser links.

I'm looking for a quick and easy way to have a column of actual links.

And it's Excel 2002. Don't judge me, please; after all, I could be using Google Docs.
LVL 15
Eric AKA NetminderAsked:
Who is Participating?

in a helper's column, you could use the function HYPERLINK


I think  if you just add http:// at the begginning of the text, Excel will convert text automatically to a link. So I would add a column and put a formula "="http://" + A1" (without the quotes) and extend that formula for all the fields.

Anyway, cCan you attach the .xls file so I can see if there is something strange on that text that avoids to be converted to an URL ?
1) Open the vba project editor (ALT-F11)
2) Add a new module
3) Copy paste this script

Sub ChangeTextToHyperLink()
  Dim lst As Long
  Dim col As Long
  Dim c As Range
  col = 1 'Columns are numbered from 1 (= A) => enter your column number here
  lst = Application.Cells(Application.Rows.Count, col).End(xlUp).Row 'Find last row used in column
  'Now lets walk the cells in the specified column
  For Each c In ActiveSheet.Range(ActiveSheet.Columns(col).Address)
    If c.Row > lst Then Exit For ' We're done
    'This does the actual trick
    ActiveSheet.Hyperlinks.Add _
      Anchor:=c, _
      Address:=c.Value, _
End Sub

4) execute this script

and Bob's your uncle.
Eric AKA NetminderAuthor Commented:

The http: is there; I can only assume that before I received the file, the hyperlinks were removed. Your formula was close; it needs an ampersand (&) instead of a +. It didn't work perfectly, but it worked.


For some reason, Excel doesn't like your module; I kept getting a security error, even after changing the security level.


Your suggestion also worked, sorta.

I wound up using an amalgam of both gplana's and Rgonzo1971's suggestions.

Eric AKA NetminderAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.