Hyperlink haters Episode #1371 is for you! I cover the following topics: Deleting one hyperlink; Deleting a range of hyperlinks with Excel 2010 new features; Deleting all hyperlinks with a 1-line macro; How to stop Excel from creating the hyperlinks.
Transcript of the video:
Hey welcome back to the MrExcel podcast, I'm Bill Jelen.
This is episode 1371: Clear Hyperlinks.
Well I have something really cool for you, especially if you have Excel 2010 today.
You know sometimes we're typing along and they automatically create hyperlinks and we end up with these hyperlinks in the spreadsheet and we want to get rid of them.
That used to be fairly hard to do.
Used to be that we would have to go to each hyperlink right click and say Remove Hyperlink.
Right click and Remove Hyperlink.
Well hey if you happen to have Excel 2010 a couple of new features that are here if you have a selection and one or more of the cells in that selection has a hyperlink now when you right-click there is a new option at the bottom called Remove Hyperlinks, the plural version.
Look at that.
They're all just gone or out here on the Home tab under the Editing group the little eraser, there is now a Clear Hyperlinks and check this out the hyperlinks are gone right there but if you open this little drop down I can also clear hyperlinks and formats.
So I have a choice, do I get rid of the formats or the hyperlinks only leaving the formats there so lots of new ways to get rid of hyperlinks.
Now if you're in an older version of Excel and you don't have those brand new features let's bring some hyperlinks back here and we'll make some copies of that.
There is a macro way to do this, you would have to do Alt+F11 and then we just do Ctrl+G down here in the immediate window.
So we can say the following code; for each hl in activesheet.hyperlinks : hl.delete : next Press Enter and in that immediate window press Ctrl+G to get that once you're over in VBA.
Let's take a look and all of the hyperlinks are gone.
All right so that definitely is a pain.
I'm really glad that Microsoft added those great new options in Excel 2010, the Clear Hyperlinks out here under Editing or even the right click and it's not going to be there unless you have at least one hyperlink.
Also, you know the other thing and I hate to even mention this, if you're a person who likes to merge cells; I'm not a fan of merging cells and I never recommend that anyone do this but let's say that an evil co-worker of yours has merged some cells and now you have a selection that does not include the entire cell.
That hyperlink won't get removed so you have to make sure that your selection includes the entire cell.
You know also the other question is, “how do I just stop it from creating hyperlinks in the first place?” All right, right after you have a hyperlink created, check that out, that little on screen; just go to the lower left hand corner you can say don't do this one or just stop altogether or if you want complete control, control autocorrect options you can turn this off in the AutoCorrect dialog.
So you get to those let the hyperlink get created and then hover over the bottom left corner of that hyperlink.
Oh I just turned it off so it's not going to happen.
So there you have it.
To get back to that that's Alt+T+A and auto format as you type.
Alt+T+A gets you back to this in case you accidentally turn it off while you're just randomly clicking around during a podcast Okay, well hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
This is episode 1371: Clear Hyperlinks.
Well I have something really cool for you, especially if you have Excel 2010 today.
You know sometimes we're typing along and they automatically create hyperlinks and we end up with these hyperlinks in the spreadsheet and we want to get rid of them.
That used to be fairly hard to do.
Used to be that we would have to go to each hyperlink right click and say Remove Hyperlink.
Right click and Remove Hyperlink.
Well hey if you happen to have Excel 2010 a couple of new features that are here if you have a selection and one or more of the cells in that selection has a hyperlink now when you right-click there is a new option at the bottom called Remove Hyperlinks, the plural version.
Look at that.
They're all just gone or out here on the Home tab under the Editing group the little eraser, there is now a Clear Hyperlinks and check this out the hyperlinks are gone right there but if you open this little drop down I can also clear hyperlinks and formats.
So I have a choice, do I get rid of the formats or the hyperlinks only leaving the formats there so lots of new ways to get rid of hyperlinks.
Now if you're in an older version of Excel and you don't have those brand new features let's bring some hyperlinks back here and we'll make some copies of that.
There is a macro way to do this, you would have to do Alt+F11 and then we just do Ctrl+G down here in the immediate window.
So we can say the following code; for each hl in activesheet.hyperlinks : hl.delete : next Press Enter and in that immediate window press Ctrl+G to get that once you're over in VBA.
Let's take a look and all of the hyperlinks are gone.
All right so that definitely is a pain.
I'm really glad that Microsoft added those great new options in Excel 2010, the Clear Hyperlinks out here under Editing or even the right click and it's not going to be there unless you have at least one hyperlink.
Also, you know the other thing and I hate to even mention this, if you're a person who likes to merge cells; I'm not a fan of merging cells and I never recommend that anyone do this but let's say that an evil co-worker of yours has merged some cells and now you have a selection that does not include the entire cell.
That hyperlink won't get removed so you have to make sure that your selection includes the entire cell.
You know also the other question is, “how do I just stop it from creating hyperlinks in the first place?” All right, right after you have a hyperlink created, check that out, that little on screen; just go to the lower left hand corner you can say don't do this one or just stop altogether or if you want complete control, control autocorrect options you can turn this off in the AutoCorrect dialog.
So you get to those let the hyperlink get created and then hover over the bottom left corner of that hyperlink.
Oh I just turned it off so it's not going to happen.
So there you have it.
To get back to that that's Alt+T+A and auto format as you type.
Alt+T+A gets you back to this in case you accidentally turn it off while you're just randomly clicking around during a podcast Okay, well hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
