Excel - How to Quickly Convert Text URLs to Clickable Hyperlinks in Excel with VBA - Episode 2665

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 22, 2025.
Microsoft Excel Tutorial: Jody needs a way to convert a column of text URLS into clickable hyperlinks in Excel.

Here is the macro:
VBA Code:
Sub MakeSelectedHyperlinksHot()
    For Each Cell In Selection
    ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:=Cell.Value, TextToDisplay:=Cell.Value
    Next Cell
End Sub

In this video, we tackle a common Excel issue: converting thousands of text strings that look like URLs into clickable hyperlinks. Jody reached out with a situation—5,000 rows of exported data in Excel, all containing text that resembles hyperlinks. The manual method of right-clicking each cell and creating hyperlinks one by one is tedious and inefficient. Is there a faster way? Absolutely. Let's explore several solutions, including a powerful VBA macro.

First, we discuss a manual trick using the F2 key to edit the cell and press Enter, which works but is impractical for large datasets. Next, we explore the HYPERLINK formula, which creates clickable links but requires keeping both the formula and the original data. While helpful, this method isn't ideal for everyone. The best approach involves using a VBA macro to automate the process, converting all 5,000 rows into hyperlinks in just seconds.

For those unfamiliar with VBA, don’t worry! We walk through creating a Personal Macro Workbook to store reusable macros. Starting from scratch, you'll learn how to enable the Developer tab, record a simple macro, and access the VBA editor. From there, we provide a concise, pre-written macro that you can copy from the video description. This macro loops through your selected cells, converting text into hyperlinks effortlessly.

Once the macro is ready, running it is as simple as selecting your data, navigating to the Macros menu, and clicking “Run.” In our demonstration, processing 2,500 rows took just four seconds! Whether you’re dealing with a few hundred or thousands of rows, this method saves you hours of manual work.

Finally, we touch on additional tips for downloading images linked to your URLs directly into Excel using the new IMAGE function in Microsoft 365. Whether you need clickable links or embedded visuals, this video equips you with the tools to handle the task efficiently. Let us know in the comments if you have specific needs or questions, and we’ll help you find the best solution. Thanks for watching, and see you next time on the MrExcel channel!

Table of Contents
(0:00) Problem Statement 5000 Text URLS in Excel need to be clickable
(0:28) Using F2, Enter, Down Arrow, 5000 times
(0:40) Using HYPERLINK() function
(1:05) Creating a Personal Macro Workbook by recording HelloWorld
(1:52) Adding Developer Tab to Ribbon
(2:09) Opening the Visual Basic Editor from the Developer tab
(2:30) Pasting the macro
(2:50) Save Personal Macro Workbook and return to Excel
(3:00) Running the macro for selected cells
(3:12) Running the macro for 2500 cells
(3:37) Using IMAGE function to download all images into the Excel workbook
(4:00) Ctrl+Shift+F5 for larger preview
(4:32) Wrap up

Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel

This video answers these common search terms:
Convert text to hyperlinks in Excel
Create clickable links in Excel VBA
Automate hyperlink creation in Excel
Excel macro to make hyperlinks active
How to convert URL text to links in Excel
Use VBA to create hyperlinks in Excel
Fastest way to make hyperlinks in Excel
Excel tips for managing text URLs
Hyperlink function vs VBA in Excel
Excel tutorial for hyperlink automation
maxresdefault.jpg


Transcript of the video:
Today we have 5,000 things that look like URLs, but they're just text in Excel. We need to make all of those clickable.
Today's question from Jody. 5,000 rows exported to Excel.
He needs to be able to click the hyperlink in the cells, but they just come in as text.
Oh, Jody: “Right-click the cell, go to the hyperlink, copy the text, paste as a link.” But you have to do each one individually. It's just super time consuming and annoying.
Is there a faster way to do this? Okay, so if I had to do this.
The first thing I'm going to do is F2 to edit. And then press Enter.
And that's going to convert them. But even with 5,000, that's just going to be miserable. There is another way.
With =HYPERLINK. They want to know where's the link location.
Where's the friendly name? And that becomes a clickable hyperlink.
It works, but you can't copy and paste as values. You have to keep both the original and this one.
So I don't like that method. I have a better way.
The way involves macros A VBA macro. And there's a good chance, Jody, that you do not have a personal macro workbook yet. So we're going to create one.
It's really simple. I just start from any blank Excel workbook.
On the View tab, come to Macros. And Record a Macro.
We're just going to call it HelloWorld. And we're going to store it, not in This Workbook, but in the Personal Macro Workbook. Click okay.
Alright, so now the macro recorder is running. Just do anything.
Type “Hello”. That's all you need to do.
And then under View, Macros, Stop Recording. And that has created a personal macro workbook for you. Which allows us to take code.
And paste it into that personal macro workbook. I can close this workbook.
Now down in the YouTube description, there is a tiny little four line macro there.
I want you to copy that. Okay? And then we're going to have to get to VBA.
To do that, you need the Developer tab in the ribbon.
Which I'm pretty sure you don't have. So Right-click and say Customize the Ribbon.
Find Developer and check it. There you go.
And then on the Developer tab, we will click Visual Basic.
It's going to start out with this big gray screen like this.
View, Project Explorer. And you'll see there's your personal.xlsb.
You can expand that. Expand Modules.
And there's that module we just recorded. With a macro that we're never going to use.
Okay, so from the YouTube description, you're going to copy these five lines of code.
This is the name of the macro, Make Selected Hyperlinks Hot.
We're going to loop through each cell that you select.
And we're going to add a hyperlink. The anchor is the cell itself.
The address is the cell value. And the text is display is the cell value.
Next cell. Alright, so at this point you can click Save to save your Personal.XLSB. And Close VBA.
And then here's how it works. I'm just going to choose a bunch.
You're going to choose all 5,000. And we'll, let's see, on the View tab, Macros, View Macros. There's your Make Selected Hyperlinks Hot.
And click run. Wow, look at that.
It's really, really fast. If we had to do, I only have like 2,500 here.
And the F8 is my quick way to get in there. Click run.
And what was that? 4 seconds? And you're good to go.
Alright, so using VBA to very quickly take hyperlinks that are just text and make them into real hyperlinks. Okay?
But Jody. Going back to your original question.
You say that you need to click on all 5,000 of these.
What are you going to do? Are you trying to download these?
If you're trying to download them. There's an amazing thing here.
If you have the latest Microsoft 365. You can actually download the image right to the workbook using the new IMAGE function. So here's the source.
That will go out and download. That is really, really small right now, but it's downloading it at full size. You can actually just copy this down and it will go out and download all of those. If you just need to see them. Just choose any one image.
And press Control+Shift+F5 to bring up a preview. Press Escape to get rid of it.
Do that over and over and over. Control+Shift+F5, Escape.
Or if you just need to get 'em into your hard drive, we can actually crack open the Excel file and pull those images straight out. So there's lots of options there.
Let me know down in the YouTube comments what you're trying to do.
And there might be a faster, faster way to go. Alright, so there we go.
A very common problem. You get a bunch of text hyperlinks down.
You need to make it mid to live hyperlinks. Creating a personal macro workbook.
And then copying some code into that workbook. Running that code.
Very fast way to solve this problem. I want to thank Jody for sending that question in.
And I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
 
Last edited by a moderator:

Forum statistics

Threads
1,226,269
Messages
6,189,956
Members
453,584
Latest member
daihoctuxaeptit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top