Excel Tutorial: How To Convert Plain Text Links Into HyperLinks

January 5th, 2008 · 78 Comments · Computer Technology & Technical Support

Are you an Excel user? Have you ever wondered how to convert an entire column of plain text links into hyperlinks without having to double click each one individually?

Don’t look in the Excel Help files for this one, it’s not even mentioned there. The following code is exactly what you need.

Convert Selected Plain Text Links To HyperLinks

Public Sub Convert_To_Hyperlinks()
    Dim Cell As Range
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

Convert Selected HyperLinks To Plain Text

Public Sub Convert_From_Hyperlinks()
    Intersect(Selection, ActiveSheet.UsedRange).Hyperlinks.Delete
End Sub

Instructions On How To Create The Macro

Step 1: From within your Excel workbook, open the macro editor by pressing ALT+F11.

Step 2: In the Tools Menu, left-click View and select Project Explorer.
View -> Project Explorer (CTRL+R)

Excel Macro Step 2

Step 3: Right-click the Project you are working on.

Step 4: Right-click ‘Modules’, select ‘Insert’, then select ‘Module’.
Modules -> Insert -> Module

Excel Macro Step 2

Step 5: Paste the code into the project module you have selected.

Step 6: Press ALT+F11 to return to your Excel workbook (or click on its icon in the Windows taskbar).

Run The Macro

Step 1: To execute the macro you just created, select the plain text links you want to convert to hyperlinks.

Step 2: Press ALT+F8 to open the Macro selector window and click on the macro you just created.

Note: ALT+F11 means you press AND HOLD the ALT key at the same time you press the F11 key.

[tags]Microsoft Excel Tutorial, Visual Basic, Hyperlink, Macros[/tags]

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Tags:

78 responses so far ↓

1 Tom // Mar 13, 2009 at 2:49 am

Hey, this is very handy little tutorial! You saved me much precious time, especially as this is something I seem to need to do quite often!

Thanks!
2 Jerry Leventer // Mar 14, 2009 at 9:11 pm

Thanks for letting me know you found it useful. There are some very nice things you can do with Excel.
3 Barbara // Mar 25, 2009 at 10:27 am

i’m struggling to get this right. I thought i followed your instructions, copied and pasted the macro, hit run and nothing happened. It’s driving me nuts.
4 Jerry Leventer // Mar 25, 2009 at 9:17 pm

Barbara – How experienced with Excel are you? This is an advanced tutorial and not meant for first time Macro users.

You may need to refer to Microsoft for more basic understanding of how to implement a macro.
5 Toby Tyler // May 29, 2009 at 8:13 am

Worked great!!! Thank you very much.
6 Jerry Leventer // May 30, 2009 at 3:35 pm

Excellent, I’m glad to hear the suggestions were helpful to you.
7 Brandon // Aug 1, 2009 at 2:52 pm

You just saved me an incredible amount of time and a nasty headache to boot. Thanks for sharing.
8 Rich Hoeg // Oct 9, 2009 at 4:03 am

You are my savior with this code … thank you!
9 Jerry Leventer // Oct 9, 2009 at 11:24 am

This seems to be a popular topic, and I appreciate all your comments. Please let me know if you have any other questions on this or other technical, or Internet marketing issues. I can’t promise I’ll be able to answer, but maybe someone else will.
10 Rob Bremel // Nov 1, 2009 at 10:45 am

Very helpful.
F2 + Enter also works but you can’t do a whole range of cells.
11 vincent // Nov 2, 2009 at 2:29 am

thanks mate, you saved me quite some work.
vincent´s last blog ..Met Alli Pillen Snel Verantwoord Afvallen | Normaal Gezond Eten
12 John P // Jan 7, 2010 at 9:28 am

You are a godsend.
I have thousands and thousands of these to convert.
13 Jerry Leventer // Jan 7, 2010 at 10:26 pm

Rob,

F2 + Enter does not seem to work in Excel 2003.
14 Family Computer Club // Jan 16, 2010 at 8:13 pm

Many people may not be able to write such a ‘sophisticated macro. You can also try this out: Select all cells. Click ‘copy’. Then use ‘paste special…’ and select values and paste in another worksheet. Data remains intact with no ‘hyperlinks’.
15 Rich // Feb 24, 2010 at 11:15 am

AWESOME
Saves so much time.
And your instructions are perfectly EASY!
Great job!
:-)
16 pcrudy // Apr 4, 2010 at 8:18 am

i run the macro in 2007 but it converts the text hyperlink to something like files:///documents and settings etc. how do you get the hyperlink pasted into another column or file?
17 Jerry Leventer // Apr 27, 2010 at 2:06 pm

PC Rudy, What is the original hyperlink pointing to?
18 LEE@Detox your body // May 3, 2010 at 11:49 am

Thanks Jerry you the man! Really helped me out, also if any ones interested you can save the macros so you can use them on any workbook in the future, may be useful to link to a post how to do that, i found it on google by searching for “saving macros”
LEE@Detox your body´s last blog ..ROR Sitemap for http://www.novadetox.co.uk/
19 Yossarian // May 11, 2010 at 5:41 am

Hi Jerry,

This macro seems almost perfect for my requirements but the links point to my desktop.

How can I specify a url for the link to go to?

If this can work then thank you SO much!
20 MK // May 26, 2010 at 12:52 pm

This works great. Just the thing I needed. Thanks much.
21 Dave // Jun 24, 2010 at 8:04 am

That worked great Jerry. I learned something today! Thanks.
22 GW // Jul 9, 2010 at 7:56 pm

I have spread sheets with email addresses in them, sometimes as links and sometimes not. I need to both convert and unconvert. Your Hyperlinks to Plain Text routine takes the links out nicely. Your Plain Text to Hyperlink routine appears at first to work, too, but it actually inserts links that look like references to files….something like this…..file:///c:\(folder)\name@isp.ext
23 Andreas Strucker // Oct 11, 2010 at 1:08 am

Thanks for this. I just imported a long list of URLs into a spreadsheet and didn’t want to go through double clicking them all. This was just what I needed. Gonna try it out and see if I run into the same problem as the commenter above me.

hopefully not. lol
Andreas Strucker´s last blog ..waste king garbage disposal- 8000 vs 9980
24 David Stuart // Oct 19, 2010 at 2:59 am

Brilliant. I don’t even want to think about how many links I’ve been double-clicking and copy & pasting…many many many thousands….I just wish I’d come across this years ago…
25 Khris Kramer // Oct 28, 2010 at 9:52 am

Thank You!! You saved me much work!
26 Jean-Charles // Nov 2, 2010 at 5:56 am

Many thanks for sharing this great piece of code !
27 Nick // Nov 29, 2010 at 6:35 pm

Cheers Jerry, worked like a charm. I really need to start learning VBA script properly.. they save incredible amounts of time, but at the moment I find writing them takes up just as much time as doing things manually would :(

@pcrudy: you need to type in the complete website, ie. ‘http://www….’, not just ‘www….’
28 Sid // Dec 14, 2010 at 7:56 am

This is great, but is it possible to use one column of links as the hyperlinks for a different column of text. In other words column A is descriptive text, column B is the actual a link, but can we combine them both into a single column such that the text in column A is displayed, but the link from column B is used for the hyperlink? Column B can then be discarded.
This would be amazing!!
29 vasil // Dec 20, 2010 at 6:32 am

Thanks a lot!
30 Sri Pasula // Jan 13, 2011 at 9:53 am

This is very helpful!
Thank you very much.
31 Rick // Jan 19, 2011 at 11:22 am

Thank you! You save me a lot of work!
32 Shane // Jan 31, 2011 at 11:01 am

F2+Enter’ing kinda stinks. Thanks a Million for the tutorial. I have absolutely NO programming skills, and this was a CINCH. Thanks!!!
33 Daniel Owens // Feb 4, 2011 at 7:48 pm

Nice post. The thing I can’t figure out is how to make the anchor text say “link” so the whole URL doesn’t show.
34 Henry // Feb 15, 2011 at 10:44 am

Thanks! You just saved me almost an hours work!

Does exactly what it says on the tin!
Henry´s last blog ..Post is coming
35 Berry // Mar 15, 2011 at 11:00 pm

This was a great tutorial. I am running a newer version of Excel but figured it out fine.

Thanks!

What’s great is you wrote that in 2008 and here it is working for us today. Thanks, again.
36 Jerry Leventer // Mar 16, 2011 at 3:21 pm

I’m glad to hear it was helpful for you.
37 sakshi mittal // Mar 21, 2011 at 11:28 pm

thanks..

great post

cheers
38 Tag Photos // Apr 12, 2011 at 6:07 am

Thank you so much. you saved my day!
Tag Photos´s last blog ..Tag Your Friends as Best Friends Forever
39 Lee // Apr 15, 2011 at 8:11 pm

thank you
40 Matthew McSorley // May 3, 2011 at 12:47 am

This is exactly what I was searching for. Thank you sir!
41 Sydney Monis // May 11, 2011 at 12:25 am

Wow, worked like a charm. You saved me a lot of time and effort. Great Tutorial.
Thanks.
42 Alan Underkofler // Jun 21, 2011 at 9:38 am

This was a great post! I received an error as one line had a site which would not convert. I had to redo the macro and start on the next line. Thank you for the post!
Alan Underkofler´s last blog ..Shocking Sales Statistics as It Relates to Follow Up
43 Arvind // Jul 7, 2011 at 1:40 am

I am so so thankful for the wonderful little code that you have presented. It worked wonders and saved precious time for me. Thanks a million!!!
44 David Font // Jul 7, 2011 at 6:51 am

A great post! Thanks for your help!

Regards.
45 Leonard // Jul 10, 2011 at 2:13 pm

awesome, I downloaded this code , maybe a year or two ago, that i may use it on thousands of karaoke files i have a xl spreed sheet for, and what’ll ya know, works like a charm, all good.
46 Jorge // Jul 20, 2011 at 11:29 pm

Thanks! This macro was really useful, I was trying to do it differently but this saved me a lot of time as the official docs are useless.
47 Johnny // Jul 24, 2011 at 3:20 pm

Thank you so much for this!
48 john // Jul 25, 2011 at 5:12 am

Thanks mate…great code!!
49 A2b // Aug 26, 2011 at 12:07 am

I’m sorry, but all of my adresses were incomplete, how can i make them all complete, i can’t do it manually since i have more or less 1000 links to do !
50 Julie // Aug 26, 2011 at 7:41 am

This is wonderful. Thanks so much!
51 BillM // Sep 13, 2011 at 12:38 pm

Great solution. However, I expected a column of URL addresses to convert to usable website links. This program will not work for this. It converts the URL into a hypertext link to a URL file name in the directory where the Excel spreadsheet is located.
52 Jerry Leventer // Sep 14, 2011 at 11:02 pm

Please try this:

Sub AnchorText_2Hyperlink()
‘
‘ 12/17/2010
‘
‘ —————————————–
‘ HOW TO USE:
‘ —————————————–
‘ Cell Contents must have a .tld, [ domain.net ] but need not have www. or http://
‘ Select one or more contiguous cells in one column only.
‘
‘ To Remove Hyperlinks, use: Remove_Hyperlinks()
‘
‘
On Error GoTo Err
With ActiveSheet
For Each cl In Selection
Do While InStr(1, cl.Value, “ ”)
cl.Value = Replace(cl.Value, “ ”, “”)
Loop

If Len(Trim(cl)) <> 0 Then
Select Case Left(LCase(Trim(cl)), 4)
Case “www.”
cl.Value = “http://” & Trim(Trim(cl.Value))
Case Is <> “http”, Is <> “www.”
cl.Value = “http://www.” & Trim(cl.Value)
End Select
.Hyperlinks.Add Anchor:=cl, Address:= _
Trim(cl.Value), TextToDisplay:=Trim(cl.Value)
End If
Next
End With

Exit Sub
Err:
MsgBox Err.Description
End Sub
53 Shipitta // Sep 22, 2011 at 2:44 am

I really need to start learning VBA script properly..
Shipitta´s last blog ..angry birds pc download
54 jonasmite // Sep 26, 2011 at 4:04 am

wow! this tutorial is very helpful to me!! thank you!! but what version of excel are you using bro?! can you help me on merge and unmerging cells and hot to use the function! plssss!!
jonasmite´s last blog ..free single dating sites
55 Alex // Oct 2, 2011 at 3:27 am

Jerry, huge thanks!
56 Cris // Oct 6, 2011 at 2:27 pm

You just saved me 30 min or more. Thank you!
57 Jerry Leventer // Oct 6, 2011 at 3:40 pm

I’m glad it was helpful.
58 Goodpersonbaddoing // Oct 12, 2011 at 7:25 am

I don’t even want to think about how many links I’ve been double-clicking and copy and pasting…many many many thousands…
Goodpersonbaddoing´s last blog ..advice for angry birds
59 sasuke05 // Oct 12, 2011 at 8:00 am

What a nice blog. Thanks for sharing. GREAT! | :P
sasuke05´s last blog ..dirt bike games for kids
60 rain0506 // Oct 16, 2011 at 8:03 pm

Nice idea, I can now easily perform my task on: How To Convert Plain Text Links Into HyperLinks, thanks for sharing this informative blog.
rain0506´s last blog ..vegan diet plans
61 LandSurveyor // Oct 17, 2011 at 4:36 pm

Worked like a charm! Thanks so much.
62 Amy Engler // Oct 18, 2011 at 10:10 am

Thanks so much for this tip! I’ve been needing these macros this for a long time!
63 avatar0506 // Oct 20, 2011 at 8:12 am

awesome, I downloaded this code , maybe a year or two ago, that i may use it on thousands of karaoke files i have a XL spreed sheet for, and what’ll ya know, works like a charm, all good. | :P
avatar0506´s last blog ..monster truck racing games
64 kavabsoo // Oct 23, 2011 at 5:53 am

this will be a great help!! but one thing, you should update you picture tutorial because the version of excel is so out dated! great post though!!!
kavabsoo´s last blog ..advice on dating
65 Ellen // Nov 7, 2011 at 10:58 pm

Thanks a lot for the tutorial. I have absolutely no programming skills, and this was a cinch. Thanks :)
Ellen´s last blog ..angry birds tips
66 ducati56 // Nov 11, 2011 at 6:58 am

I expected a column of URL addresses to convert to usable website links. This program will not work for this. It converts the URL into a hypertext link to a URL file name in the directory where the Excel spreadsheet is located. | :P
ducati56´s last blog ..games for boys
67 Iynn@uncleaimedfunsnewyork // Nov 16, 2011 at 7:48 am

Thanks for sharing this very great tips.. :)
68 Mike // Nov 27, 2011 at 7:24 am

Couldn’t get it to work for some reason in 2007. This works for me. It takes text hyperlinks in col 1 and put html link in col2, like ABC News in col1 and converts to www.abcnews.com/latestnews.com, which is value of text link.

Sub Test()

For Each hl In ActiveSheet.Hyperlinks

Cells(hl.Parent.Row, 2).Value = hl.Address

Next hl

End Sub
69 Mike // Nov 27, 2011 at 9:01 am

Sorry, left a few details out. In example above I have my text hyperlinks in column 1 and it will place the html hyperlinks into column 2.
70 Mark // Nov 30, 2011 at 8:04 am

Thank you for providing! Works smoothly and shortest macro I saw when looking.
71 Mike @ SWDS // Dec 16, 2011 at 6:54 am

Brilliant. You do not know how many times I have double clicked to make a hyper link. I have done thousands and thousands. Thank you very much.
72 dan mares // Dec 21, 2011 at 10:18 am

the macro seems to work well on small data sets. but we routinely process over 60,000 records. and it (i assume its VB) blows up every time. We are using Excell 2010.
73 Pankaj Anupam // Dec 26, 2011 at 2:05 am

Very helpful.

Format painter also works. You can do a whole range of cells.
74 Jood42 // Jan 4, 2012 at 9:34 am

Thanks so much! This really helped.
75 GENLiS // Jan 18, 2012 at 7:01 am

Great!! Thanks ;)
76 Drainpop // Jan 22, 2012 at 9:49 pm

Works great!!!
You can not imagine how much you have help me!!!
I though I had to modify 5000 hyperlinks by hand…
THANKS SO MUCH
77 Didier // Feb 2, 2012 at 6:15 am

Why not just use the function =HYPERLINK().
78 Jerry Leventer // Feb 2, 2012 at 6:55 pm

Back in 2008, I was not familiar with that method, but it does seem to work quite well. Just create a column to the right and enter =hyperlink(A1), drag it to the match the rows you are selecting in column A.

Jerry Leventer