Today, Bill continues to look at Hyperlinks in Microsoft Excel - like when you paste a column of website addresses into Excel, none of them automatically turn into a hyperlink. Episode #1361 looks at a couple of solutions to make your hyperlinks hot.
Transcript of the video:
Welcome back to the Learn Excel podcast.
I'm Bill Jelen from MrExcel.com, this is episode 1361: Hyperlinks aren't Hot?
Well hey welcome back to the MrExcel netcast.
We've been talking about hyperlinks and you know, usually if you go to a cell and you type a website address and press Enter it automatically changes to a hyperlink.
Now we're not here to talk about whether or not you like that or not; that'll be in another episode but today I'm talking about the people who want the hyperlinks but they don't type all their data.
I'm going to switch over here to notepad, Ctrl+C and come back.
So you got some data from somewhere else; maybe from word, maybe from notepad, maybe from an email right and you paste and none of the hyperlinks are hot.
Oh what a pain this is.
Now, if you have three or four or five of them F2 to edit and press ENTER and you're good but if you have a list of a hundred of these or a thousand these or a million of these no way you can do that.
So here's what we're going to do.
We're going to do equal, there's a great function called HYPERLINK; people say, “well what's the HYPERLINK function for?” The HYPERLINK function is for this very situation.
What's the link location?
That’s this address over here.
What's the friendly name?
Well I'm not going to type friendly names I'm just going to use the link location.
=HYPERLINK(B5,B5) I’m going to double-click this filler in here, the square dot to shoot that down, copy down and there you go I now have hyperlinks although you have to be really careful about this.
Notice in my original example up here I just type www.mrexcel.com and it worked.
If your text doesn't have the HTTP colon slash, slash it's not going to work.
Here watch; we will follow this link and it works fine.
We'll follow the other link and no deal, oh.
So let's talk about that.
If you have just www without the HTTP colon we can solve that.
Here's what we're going to do we're going to build a link location.
In quotes HTTP colon slash, slash closing quote and then an ampersand.
An ampersand is the Excel character to join two bits of text together.
=HYPERLINK(“http:”&B9,B9) All right, so now that link will work.
So just watch out for that situation where you don't have the HTTP colon.
All right, now we have two sets of these.
This over here is not hot, this over here is hot.
We want to keep the hot set.
Here's what you do.
You choose that whole set; I go here and say Ctrl+ Shift Down Arrow to select the whole range we're going to copy and then I'm going do it the old-fashioned way.
This will work in any version of Excel.
Open the paste drop-down and go to paste special.
In the paste special dialog choose values, click OK and that will convert those formulas.
Yes, here we don't have a formula anymore, we just have the actual hyperlinks.
We now copy and paste back over the original data and everything that's good.
Yes, there's faster ways to paste values in Excel 2010 and even different in Excel 2007.
I just used paste special there in case you're new to Excel leave me to find the reliable way to do that.
So there you have it hyperlinks that you paste in from somewhere else don't automatically get hot you could use F2+Enter or that great little hyperlink function to solve the problem.
Well hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen from MrExcel.com, this is episode 1361: Hyperlinks aren't Hot?
Well hey welcome back to the MrExcel netcast.
We've been talking about hyperlinks and you know, usually if you go to a cell and you type a website address and press Enter it automatically changes to a hyperlink.
Now we're not here to talk about whether or not you like that or not; that'll be in another episode but today I'm talking about the people who want the hyperlinks but they don't type all their data.
I'm going to switch over here to notepad, Ctrl+C and come back.
So you got some data from somewhere else; maybe from word, maybe from notepad, maybe from an email right and you paste and none of the hyperlinks are hot.
Oh what a pain this is.
Now, if you have three or four or five of them F2 to edit and press ENTER and you're good but if you have a list of a hundred of these or a thousand these or a million of these no way you can do that.
So here's what we're going to do.
We're going to do equal, there's a great function called HYPERLINK; people say, “well what's the HYPERLINK function for?” The HYPERLINK function is for this very situation.
What's the link location?
That’s this address over here.
What's the friendly name?
Well I'm not going to type friendly names I'm just going to use the link location.
=HYPERLINK(B5,B5) I’m going to double-click this filler in here, the square dot to shoot that down, copy down and there you go I now have hyperlinks although you have to be really careful about this.
Notice in my original example up here I just type www.mrexcel.com and it worked.
If your text doesn't have the HTTP colon slash, slash it's not going to work.
Here watch; we will follow this link and it works fine.
We'll follow the other link and no deal, oh.
So let's talk about that.
If you have just www without the HTTP colon we can solve that.
Here's what we're going to do we're going to build a link location.
In quotes HTTP colon slash, slash closing quote and then an ampersand.
An ampersand is the Excel character to join two bits of text together.
=HYPERLINK(“http:”&B9,B9) All right, so now that link will work.
So just watch out for that situation where you don't have the HTTP colon.
All right, now we have two sets of these.
This over here is not hot, this over here is hot.
We want to keep the hot set.
Here's what you do.
You choose that whole set; I go here and say Ctrl+ Shift Down Arrow to select the whole range we're going to copy and then I'm going do it the old-fashioned way.
This will work in any version of Excel.
Open the paste drop-down and go to paste special.
In the paste special dialog choose values, click OK and that will convert those formulas.
Yes, here we don't have a formula anymore, we just have the actual hyperlinks.
We now copy and paste back over the original data and everything that's good.
Yes, there's faster ways to paste values in Excel 2010 and even different in Excel 2007.
I just used paste special there in case you're new to Excel leave me to find the reliable way to do that.
So there you have it hyperlinks that you paste in from somewhere else don't automatically get hot you could use F2+Enter or that great little hyperlink function to solve the problem.
Well hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
