Jerry Leventer

Excel Tutorial: How To Convert Plain Text Links Into HyperLinks

January 5th, 2008 · 124 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
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]


124 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!


  • 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


    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

    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 =-.

  • 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

  • 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!

  • 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.


    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


    great post


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

    Thank you so much. you saved my day!

  • 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.

  • 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!

  • 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!


  • 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, [ ] 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, ”     “, “”)

    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
    End With

    Exit Sub
    MsgBox Err.Description
    End Sub

  • 53 Shipitta // Sep 22, 2011 at 2:44 am

    I really need to start learning VBA script properly..

  • 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!!

  • 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…

  • 59 sasuke05 // Oct 12, 2011 at 8:00 am

    What a nice blog. Thanks for sharing. GREAT! | :P

  • 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.

  • 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

  • 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!!!

  • 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 :)

  • 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

  • 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, 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…

  • 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.

  • 79 Loren // Feb 8, 2012 at 7:25 am

    I love you!!!

  • 80 Jerry Leventer // Feb 8, 2012 at 8:06 pm

    It’s nice to have fans …

  • 81 Frankendog // Feb 17, 2012 at 4:38 am

    Clear and easy to understand.

  • 82 Toronto Fan // Feb 17, 2012 at 7:17 am

    You got another new fan today!

    Thanks a bunch

  • 83 Melafont // Feb 20, 2012 at 12:40 pm

    I use Excel 2010 and the “Convert_To_Hyperlinks” does not work. I am trying to convert a cell which has a cell address in it to a hyperlink to another worksheet in same workbook but all subs thus far give the message “Cannot open the specified file”.

  • 84 Eric M. // Feb 21, 2012 at 8:55 am

    Thank you so much! Worked fine!

  • 85 Helen // Mar 13, 2012 at 9:01 am

    Hi, I have managed to use your code at the top – but it is now quite what I am needing. I have a column of numbers which are reference numbers of files – I need to be able to automatically add an ‘http://blah blah ending with a ?’ to the front of the number in each cell and make that into a hyperlink. Is there a point in the code where I can join the beginning bit of the hyperlink address to the discrete ending of the file reference number?

  • 86 Jude B // Mar 29, 2012 at 2:55 pm

    To convert hyperlinks to text. Type 1 in an empty cell. Copy that cell and Paste Special over all cells that have hyperlinks. In the Paste Special dialogue box – click the Multiply radio box. All hyperlinks are converted to text.

  • 87 Richard Troiano // Apr 14, 2012 at 1:37 pm

    Jerry you rock! thank you for posting this wonderful tutorial!

  • 88 Excel 2010: Convert Selected Text into Hyperlinks « xombe // Apr 18, 2012 at 8:40 am

    […] on except… they wouldn’t turn into links.  A quick search on Google found this:  Awesome tutorial.  Follow the directions and it works […]

  • 89 Rochefort // Apr 18, 2012 at 8:42 am

    Worked perfectly. Noted your site on my blog. Thanks!

  • 90 Tim // Apr 25, 2012 at 5:49 am

    This is a great tool, THANKS! Saved me tons of time and worked perfect. The screen shots make all the difference in making it easy.

  • 91 Eric // May 2, 2012 at 10:44 am

    Awesome! Thanks for posting

  • 92 Alex Adams // May 7, 2012 at 11:47 pm

    Help, I’ve 17,500 of these to do and every ‘space’ in my file name is converted to ‘%20’ in the hyperlink? find and replace does not seem able to search hyperlinks?

  • 93 ashish // May 13, 2012 at 8:12 pm

    I ran your macro on a spread sheet in office 2010 that has like 510,000 row. While processing the colum with the text fields i receive a Macro 1004 error. Any suggestions how to rectify it?

  • 94 Tom // May 24, 2012 at 10:39 am

    Thanks, this is JUST what I was looking for! Worked great!!!

  • 95 dobeso // Jun 2, 2012 at 7:15 pm

    Thank you so much!!! This is really useful for me…
    Your explanation is also really easy, which is not common. Awesome post.

  • 96 Jerry Leventer // Jun 4, 2012 at 11:05 pm

    Screen captures with annotations can be very helpful for this type of tutorial. I borrowed the technique from others I learned from.

  • 97 chris // Jun 20, 2012 at 6:17 am

    just wanted to thank you – this is awesome!!!

  • 98 Happy chappy // Jul 6, 2012 at 12:56 am

    Thanks so much, especially from my left mouse finger and right index finger! Well explained!

  • 99 windz // Jul 11, 2012 at 10:06 pm

    Thanks a lot for the time & effort.. it’s been years after you made this and it’s still helping others. Awesome!!!

  • 100 Jen // Jul 26, 2012 at 12:39 am

    How could I change the hyperlink location without have to change it individually?
    Thank you.

  • 101 Jen // Jul 26, 2012 at 12:42 am

    Is there a way I could change the location of the hyperlink path/location without have to change it one by one? Example: I have my linked file in “K” drive (flash drive), now I want to change it to drive “C”.
    Thank you for helping me.

  • 102 Amit Vedak // Aug 4, 2012 at 1:29 am

    Simply great… Thanks

  • 103 rhinemine // Aug 27, 2012 at 7:03 am

    Great Work. Really nice.

  • 104 Jerry // Sep 11, 2012 at 10:37 am

    You rock. Thanks much :)

  • 105 Phil // Oct 14, 2012 at 12:37 pm

    I’m a first time macro user. Very clear! Works just as described. Thanks much!

  • 106 Emma // Oct 21, 2012 at 9:51 pm

    Thanks so much, this was exactly what I needed.

  • 107 How To Use Macros You Find Online In 6 Easy Steps // Nov 2, 2012 at 10:31 am

    […] Find a macro from a trusted source that you want to run on a sheet of data. I’m going to use this macro that converts URLs to hyperlinks: […]

  • 108 Kristin // Nov 28, 2012 at 2:12 pm

    Astonishing! Oh, how many times I could have used this over so many years of “trial with error!” Many thanks!

  • 109 Rod // Dec 19, 2012 at 12:36 pm

    Thanks for saving me time, this is my best find of the month.

  • 110 Rich // Jan 23, 2013 at 1:47 am

    That’s excellent. I added the macro to the Quick Access Toolbar.

  • 111 Jerry Leventer // Jan 23, 2013 at 4:46 pm

    I think it’s great that you are able to use these Excel formulas. Once you learn some of these tricks and macros, it becomes a very powerful tool. I’ve been able to do some amazingly complicated data management. I have other scripts for Excel that allow you to remove duplicates, or to compare lists that have multiple columns of data, but you can see which ones match on only one of the columns.

  • 112 Quentin // Mar 1, 2013 at 12:58 am

    Brilliant, thanks. Still proving useful long after you wrote the post!

  • 113 Peter // Mar 15, 2013 at 10:10 am

    This is great! Thanks for sharing it!

  • 114 Audrey // Mar 21, 2013 at 11:13 am

    Thanks Jerry – this tutorial was very helpful/useful!

  • 115 Gary // Apr 9, 2013 at 5:17 pm

    Great instructions. Works Great. You’re Great! Thank you.

  • 116 Andrew Szabo // Apr 29, 2013 at 11:05 am

    Worked flawlessly.

  • 117 Jerry Leventer // Apr 29, 2013 at 11:25 pm

    Good to know it’s still working. Thanks for your feedback.

  • 118 J.R. // May 5, 2013 at 9:35 pm

    This worked great, thanks for the help. Is there anyway to save the macro for future use, so I don’t have to create it for every excel sheet I am working on? Thanks.

  • 119 Jerry Leventer // May 6, 2013 at 12:21 pm

    In order to use the macro for future spreadsheets, save the macro in a spreadsheet called My_Macros.xls.

    As long as that file is open when you work with other spreadsheets, when you click ALT+F8, you will see a list of macros you want.

  • 120 Greg // Jul 22, 2013 at 8:25 am


    Great instructions worked in MS Excel 2010. I put in my personal xlsb file.


  • 121 P Woolard // Aug 13, 2013 at 11:59 am

    Thanks for keeping this jewel available all these years later. I just found this tutorial. Now if you know how to add this cell value hyperlink to another macro in the same workbook using VBA that would be sweet.

  • 122 Ram // Sep 19, 2013 at 4:06 am

    Thanks for this code, this really saved a lot of time!

  • 123 Excel Tutorial // Oct 16, 2013 at 1:06 pm

    Nice tutorial. Exactly what I was looking for!

  • 124 Mauro // Jan 28, 2016 at 2:45 am

    Excellent tutorial. A real time saving
    Thank you very much!

Leave a Comment

Google Analytics Alternative