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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.