Microsoft Excel Tutorial: Jody needs a way to convert a column of text URLS into clickable hyperlinks in Excel.
Here is the macro:
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
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
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.
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: