Excel Tutorial: How To Convert Plain Text Links Into HyperLinks

January 5th, 2008 · 22 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:

22 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

Jerry Leventer