Have you ever had an Excel spreadsheet full of web addresses that were not clickable? Pressing F2 and ENTER is good enough when you only have a few, but when you have dozens, hundreds or even thousands, this can become a very time consuming task.
Fortunately there are a couple of approaches which will save you tons of time.
Note that neither of these two methods do any validating to ensure the result is actually a valid hyperlink.
Method #1: Use an Excel formula to create hyperlinks
This first method uses a formula to create a hyperlink enable version of the cell in an adjacent cell:
The first parameter is the cell address that contains the URL and the second refers to the text you want to have appear in the cell. Note that if your URL does not start with "http://", you will need to add it as follows:
=Hyperlink("http://" & a1, a1)
Of course you could create a more complex formula that could automatically detect whether http:// is required but those above are easier to remember.
Method #2: Using VBA to convert text into hyperlinks (advanced)
The second way is to use VBA and then run the macro. When executed, it will go through the selected range of cells and turn them all into hyperlinks. Note that this method only works if the workbook is in single user mode, not multi-user shared mode.
' Written by: Michael Milette ' Copyright 2011-2012 TNG Consulting Inc. ' Purpose: Converts the selected text into hyperlinks. ' Note: HTTP is assumed if not specified in the text. Public Sub Text_To_Hyperlink() Dim Cell As Range For Each Cell In Intersect(Selection, ActiveSheet.UsedRange) If Trim(Cell) > "" Then If Left(Trim(Cell), 4) = "http" Then ' handles http and https ActiveSheet.Hyperlinks.Add Cell, Trim(Cell.Value) Else ' Default to http if no protocol was specified. ActiveSheet.Hyperlinks.Add Cell, "http://" & Trim(Cell.Value) End If End If Next End Sub
Creating the Macro
- Open your Excel workbook
- Open the macro editor by pressing ALT+F11.
- In the View and select Project Explorer.
- Look for the folder called Microsoft Excel Objects. Right-click on it, select Insert and then Module.
- Paste the above code into the project module you have selected.
- Press ALT+F11 to return to your Excel workbook or click on its button in the Windows taskbar.
A good place to put such macros is actually in your personal workbook so that it is available regardless of the workbook you are editing. You can Google that to find out how.
To run the Macro
- To execute the macro, select the text links you want to convert to clickable hyperlinks.
- Press ALT+F8 to open the Macro selector window and click on the macro you just created.
- Your links should now be clickable.
BONUS: Using VBA to remove Hyperlinks
Another macro you might find useful helps you remove the selected hyperlinks, leaving just the text:
' Written by: Michael Milette ' Copyright 2011-2012 TNG Consulting Inc. ' Purpose: Removes the hyperlinks from the selected range leaving the text. Sub Remove_Hyperlinks() Intersect(Selection, ActiveSheet.UsedRange).Hyperlinks.Delete End Sub
2014-01-03 at 4:10 PM
2014-09-30 at 6:55 PM
I followed the steps but when running the macro, it had an error and when I tried to debug it it pointed to this line error:
ActiveSheet.Hyperlinks.Add Cell, “http://” & Trim(Cell.Value)
don’t know what to do next..can you help?
2014-10-07 at 9:39 AM
What version of Excel are you using?
2015-07-06 at 3:54 PM
The macro doesn’t run if the url is on a line other than the first, after a soft return (Alt-Enter).
2016-05-30 at 9:33 AM
this was great at first, but it only worked on one file for me and then stopped working, i get “can’t execute code in break mode” and i get the same line error as Hans above
2017-06-16 at 10:16 AM
I am having the same error in Excel version 15.33 for Mac. Any thoughts on how to fix this?
2017-10-26 at 1:39 PM
Sorry, wish I could but I can’t help you there. Don’t have access to a Mac.
2015-03-30 at 8:55 PM
Thanks so much. Method 2 worked. Method 1 did not. Method 1 keeps putting a space after the last “/”and the first “w”. No spaces in formula. Typed in exactly like the example.
Thanks again for method 2!
2015-08-08 at 11:47 AM
This was a life saver! Used Method 2. Thank you!
2015-12-26 at 11:03 AM
Perfect – Thank you!
2016-07-11 at 11:04 PM
Thank you so much. I no longer have to copy and paste hundreds of urls into my browzer!
2016-08-25 at 7:27 PM
Thank you sir! This works great.
2016-09-08 at 2:00 PM
Thanks so much for this timesaver! I’d been manually converting URLs to issue reports for our project summary report and I’d always been searching for a better way. Do you know of a way to do this is Microsoft Word?
2016-09-11 at 3:37 PM
Great question. In Excel, the script makes an assumption that each cell contains a URL. Unfortunately it would be somewhat more complicated to achieve the same results in Word because it just contains free form text. If you really wanted to pursue this approach, I would recommend you look into using regular expressions to search through the contents of the document for patterns which match URLs. By the way, regular expressions are not enabled by default so you will need to enable it. To enable this, you will need to:
1) Start the Visual Basic Editor in Word.
2) Go to Tools > References.
3) Check the box next to the “Microsoft VBScript Regular Expressions 5.5” option.
4) Click the OK button.
From this point on, you will be able to create a RegExp object in your VBA script. It will also be available through the Object Browser (F2).
You can find a good description of how to do this as well as an example of how to use it by visiting:
A much simpler approach would be to use an old feature of Word called “AutoFormat”. Although you won’t find it in any menus, the functionality still exists in current versions of Word. You can add this accessible by adding it to your Quick Access Toolbar. You’ll find good instructions on how to do that on the AddinTools website.
Once you have the tool setup, just click it and follow the prompts. By default, it will actually apply a lot more formatting than just convert your URLs. If you like that, fine. Otherwise, before clicking on the OK button, go into the options and uncheck all of the boxes in the Apply and Replace sections except for “Internet and network paths with hyperlinks” in the Replace section. Make sure this one is checked.
If you really need to achieve this using an automated VBA script, the following VBA script should produce the same results:
Dim s1 As Boolean, s2 As Boolean, s3 As Boolean, s4 As Boolean, s5 As Boolean
Dim s6 As Boolean, s7 As Boolean, s8 As Boolean, s9 As Boolean, s10 As Boolean
‘ Temporarily save the current AutoFormat settings.
s1 = .AutoFormatApplyBulletedLists
s2 = .AutoFormatApplyHeadings
s3 = .AutoFormatApplyLists
s4 = .AutoFormatApplyOtherParas
s5 = .AutoFormatReplaceFractions
s6 = .AutoFormatReplaceHyperlinks
s7 = .AutoFormatReplaceOrdinals
s8 = .AutoFormatReplacePlainTextEmphasis
s9 = .AutoFormatReplaceQuotes
s10 = .AutoFormatReplaceSymbols
‘ Only convert the URLs (same as unchecking the option boxes).
.AutoFormatReplaceHyperlinks = True
.AutoFormatApplyBulletedLists = False
.AutoFormatApplyHeadings = False
.AutoFormatApplyLists = False
.AutoFormatApplyOtherParas = False
.AutoFormatReplaceFractions = False
.AutoFormatReplaceOrdinals = False
.AutoFormatReplacePlainTextEmphasis = False
.AutoFormatReplaceQuotes = False
.AutoFormatReplaceSymbols = False
‘ Apply the AutoFormat.
‘ Restore the settings back to the way they were.
.AutoFormatApplyBulletedLists = s1
.AutoFormatApplyHeadings = s2
.AutoFormatApplyLists = s3
.AutoFormatApplyOtherParas = s4
.AutoFormatReplaceFractions = s5
.AutoFormatReplaceHyperlinks = s6
.AutoFormatReplaceOrdinals = s7
.AutoFormatReplacePlainTextEmphasis = s8
.AutoFormatReplaceQuotes = s9
.AutoFormatReplaceSymbols = s10
Hope this helps.
2016-10-11 at 4:26 PM
This worked great for me but I changed it to https:// because that is what I needed the problem with that is https:// requires a port number after and its port 8443 how could I write that in to accommodate that.
2016-10-22 at 12:27 AM
Hi Kathleen, glad to hear you found this useful. As for the port, it is added after the domain. For example:
If it is not already there, you will need to write some VBA to insert the colon and the port number before the third forward slash (/).
Option 2: Use the .substr(8) method to trim off the https:// from the URL. Then use the .replace(searchStr, replaceStr) method to replace the first forward slash with “:8443/”. Finally, prepend the string with https://.
Hope this helps. Best regards,
2016-11-02 at 4:47 PM
Hello, came across this the other day and when confronted with this task and have found very useful. Thank you for posting.
Could you please post a version where it also inserts a word or phrase to display instead of having the link itself be the displayed text value? I have users who are OK with seeing “Click Here” in the cell and clicking on it and having it work, but they don’t want to see a very long link (not to mention it takes up a lot of visible space) as the displayed text value.
2016-11-16 at 4:37 PM
Hi Tim, thank you for the great question. I am glad to hear you found the article very useful.
To achieve your desired outcome, just add the following line between the first and second “End If” in the Method #2 VBA code.
Cell.Value = “Click Here”
That’s all there is to it. The visible link will be replaced by the string “Click Here” but the link will remain available if you click on it.
IMPORTANT NOTE: After you run the modified code once, the text in the cell will no longer be the URL. DO NOT run the VBA code a second time or you may lose the link. Let me know if you want a more detailed explanation.
Hope this helps.
2016-11-20 at 5:29 AM
Thanks alot for well job,It works great
2017-01-17 at 9:02 AM
Thank you Michael 🙂
2017-01-23 at 4:53 PM
You are most welcome Sanjit! 🙂
2017-02-08 at 6:25 PM
Good stuff, you just saved me a ton of grunt work.
2018-01-09 at 11:36 AM
Method 2 with the C/P Macro worked a treat – thanks very much. I had a number of cells with mixed content ie descriptive text and hyperlinks – those threw up an error so I had to copy them to another cell.
Error was at line:
If Left(Trim(Cell), 4) = “http” Then ‘ handles http and https
Would there be a way of skipping text prefaced with a link and continuing to apply the macro at links?
2020-04-19 at 12:01 PM
thanks for this information
2022-12-20 at 7:19 PM
THANKYOU! This is exactly what I was looking for!