Today, in Episode #1362, Bill shows us how to paste links with the 'Friendly Name', and then extract the actual 'URL' from the link. Also learn how to easily remove unwanted buttons, and graphics that are inadvertently copied into your workbook.
Transcript of the video:
Learn Excel from MrExcel podcast episode 1362: Get Hyperlink Link Location.
Hey, welcome back to the MrExcel netcast I'm Bill Jelen.
We've been talking about hyperlinks this week; who would think that hyperlinks could have so many different topics but they do.
I'm actually going to switch over to Internet Explorer here and I'm going to go to a page on my sight www.mrexcel.com/articles.shtml.
This is a page I put together a while ago that lists a whole bunch of different articles available out there on the web.
Not just from my site but from the TechTV site, from the J-Walk site, from Chip Pearson site; about 500 different articles out there and let's just say I'm going to copy this data Ctrl + C to copy.
We will go back to Excel and paste, Ctrl + V.
Okay two very annoying things.
First of all, they brought all of these little images along.
My bank website does this too.
It's not the website it’s just the copy and paste and I need to get rid of those.
There used to be a great little arrow down here at the bottom of Excel 2003; it's still here in Excel 2007 or Excel 2010.
You need to go to the Home tab over here to Editing under Find and Select and right down at the bottom there's something called Select Objects.
It's a white arrow and then just draw a box around all those objects and click delete and they're gone.
Okay, now here's the real problem, the web page that I copied this from has addresses for all of these articles but they're kind of hidden underneath the link.
We're seeing the friendly name let's say, instead of the link address and I really need to get the link address.
Yeah, wow, this will be tough.
Okay first of all, see I'm still in select objects here so I need to hit the Escape key here and get out of that.
We're going to do a short little macro today.
I’m going to press Alt + F11 and then we'll go to Insert Module.
I love this macro, it’s about three lines of code.
We're going to say Sub Get links you can call that whatever you want by the way and we're say for each.
I'm going to use a variable here called HL.
HL to me stands for hyperlink in active sheet dot hyperlinks.
Funny, hyperlinks belonged to the worksheet instead of to the individual cells and we are going to say HL, that's the hyperlink dot parent, that's the cell that contains the hyperlink.
All right, so I'm looking at that cell that contains the hyperlink I am going to dot offset.
Offset says we want to move somewhere; I want to move 0 rows down, one row to the right.
Okay so that's saying wherever we find a hyperlink go to the cell immediately to the right of it and the dot value is going to equal to hl dot address remember HL is the hyperlink and address is a property of the hyperlink and then just next HL.
There we go and actually we need to go back to Excel.
When you use this make sure that every cell that has a hyperlink has a blank cell to the right of it.
This was a blank workbook when I started out so I know that I'm safe, you might want to insert a new column there if you have data.
All right, let's check this out I'm switching back to VBA and let’s just click the Run button.
Sub GetLinks() For Each hl In ActiveSheet.Hyperlinks hl.parent.offset(0.1).value = hl.address Next hl End Sub There, it's that fast.
Let's look now back in Excel.
Oh sweet.
Next to every article title there is now the article URL.
All right so this happens to me a lot.
I copied it from a web page comes in, I can't see the hyperlinks anymore; I'm not going to go edit each hyperlink to get it out but a macro will do that very quickly.
Hey I want to thank you for stopping by, we will see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast I'm Bill Jelen.
We've been talking about hyperlinks this week; who would think that hyperlinks could have so many different topics but they do.
I'm actually going to switch over to Internet Explorer here and I'm going to go to a page on my sight www.mrexcel.com/articles.shtml.
This is a page I put together a while ago that lists a whole bunch of different articles available out there on the web.
Not just from my site but from the TechTV site, from the J-Walk site, from Chip Pearson site; about 500 different articles out there and let's just say I'm going to copy this data Ctrl + C to copy.
We will go back to Excel and paste, Ctrl + V.
Okay two very annoying things.
First of all, they brought all of these little images along.
My bank website does this too.
It's not the website it’s just the copy and paste and I need to get rid of those.
There used to be a great little arrow down here at the bottom of Excel 2003; it's still here in Excel 2007 or Excel 2010.
You need to go to the Home tab over here to Editing under Find and Select and right down at the bottom there's something called Select Objects.
It's a white arrow and then just draw a box around all those objects and click delete and they're gone.
Okay, now here's the real problem, the web page that I copied this from has addresses for all of these articles but they're kind of hidden underneath the link.
We're seeing the friendly name let's say, instead of the link address and I really need to get the link address.
Yeah, wow, this will be tough.
Okay first of all, see I'm still in select objects here so I need to hit the Escape key here and get out of that.
We're going to do a short little macro today.
I’m going to press Alt + F11 and then we'll go to Insert Module.
I love this macro, it’s about three lines of code.
We're going to say Sub Get links you can call that whatever you want by the way and we're say for each.
I'm going to use a variable here called HL.
HL to me stands for hyperlink in active sheet dot hyperlinks.
Funny, hyperlinks belonged to the worksheet instead of to the individual cells and we are going to say HL, that's the hyperlink dot parent, that's the cell that contains the hyperlink.
All right, so I'm looking at that cell that contains the hyperlink I am going to dot offset.
Offset says we want to move somewhere; I want to move 0 rows down, one row to the right.
Okay so that's saying wherever we find a hyperlink go to the cell immediately to the right of it and the dot value is going to equal to hl dot address remember HL is the hyperlink and address is a property of the hyperlink and then just next HL.
There we go and actually we need to go back to Excel.
When you use this make sure that every cell that has a hyperlink has a blank cell to the right of it.
This was a blank workbook when I started out so I know that I'm safe, you might want to insert a new column there if you have data.
All right, let's check this out I'm switching back to VBA and let’s just click the Run button.
Sub GetLinks() For Each hl In ActiveSheet.Hyperlinks hl.parent.offset(0.1).value = hl.address Next hl End Sub There, it's that fast.
Let's look now back in Excel.
Oh sweet.
Next to every article title there is now the article URL.
All right so this happens to me a lot.
I copied it from a web page comes in, I can't see the hyperlinks anymore; I'm not going to go edit each hyperlink to get it out but a macro will do that very quickly.
Hey I want to thank you for stopping by, we will see you next time for another netcast from MrExcel.
