When you copy data from a web page and paste into Excel, you often end up with a whole series of tiny graphical icons. Rather than deleting those one at a time, Episode 663 will show you how to get rid of many objects at once.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Welcome to the new year.
This is our first podcast of 2008.
I want to take a look this week, at how to get data from the web and the first step today is just simply copying and pasting the data from the web.
Tomorrow, we're then going to take a look at how to take that data that's been pasted and get the links out of it.
So I'm going to start here at MrExcel.
I want to go to the left hand Nav and use "Learn Excel Resources" and then "Articles", this is a great page that has about 585 five different articles on it.
Basically not just articles from my site but articles from J-walk and Chip Pearson and techtv.
So I'm going to just select a section of this web page here, grab the text and I'll use Ctrl+C to copy and then switch back to excel in cell A1. Hit Ctrl+V to paste.
All right now the first really annoying thing we have and this happens with so many different websites.
The websites try to be cute and add these little graphical icons in.
Well, unfortunately those icons usually get pasted into the data and it's very annoying to go through one by one by one to select them and get rid of them all.
So my trick is we're going to go to the drawing toolbar.
If you don't have the drawing toolbar shown, you want to go to "View" "Toolbars" "Drawing" and it appears at the bottom of the screen.
Let's look down here at the bottom in the drawing toolbar, the second icon is a white arrow.
It's called "Select objects" Now we turn on select objects, and then basically we're going to draw a rectangle around all of those icons and what it does is, it selects all of the icons at once.
Now I can just simply hit the delete key and everything is good.
Now if you happen to get interrupted right now and leave the computer, and then come back 5 minutes from now, you're going to be completely freaked out because nothing you do in excel work. For example, If I try and choose cell E2 it doesn't work.
If I try and right click on this hyperlink, it doesn't work.
Why? Because I'm still in select objects mode.
I have to go back down to that white arrow and Uncheck it to get back to the regular mode where I can continue to use excel.
So it's a very cool tool.
Although, watch out because if you inadvertently leave it on, nothing will work in excel.
All right. So there we go.
We have a whole series of links.
Now tomorrow, I'm going to take a look at how we can take that data and actually extract the links from each cell and put it in a new column.
Thanks for stopping by. Will see you next time for another netcast from MrExcel.
Welcome to the new year.
This is our first podcast of 2008.
I want to take a look this week, at how to get data from the web and the first step today is just simply copying and pasting the data from the web.
Tomorrow, we're then going to take a look at how to take that data that's been pasted and get the links out of it.
So I'm going to start here at MrExcel.
I want to go to the left hand Nav and use "Learn Excel Resources" and then "Articles", this is a great page that has about 585 five different articles on it.
Basically not just articles from my site but articles from J-walk and Chip Pearson and techtv.
So I'm going to just select a section of this web page here, grab the text and I'll use Ctrl+C to copy and then switch back to excel in cell A1. Hit Ctrl+V to paste.
All right now the first really annoying thing we have and this happens with so many different websites.
The websites try to be cute and add these little graphical icons in.
Well, unfortunately those icons usually get pasted into the data and it's very annoying to go through one by one by one to select them and get rid of them all.
So my trick is we're going to go to the drawing toolbar.
If you don't have the drawing toolbar shown, you want to go to "View" "Toolbars" "Drawing" and it appears at the bottom of the screen.
Let's look down here at the bottom in the drawing toolbar, the second icon is a white arrow.
It's called "Select objects" Now we turn on select objects, and then basically we're going to draw a rectangle around all of those icons and what it does is, it selects all of the icons at once.
Now I can just simply hit the delete key and everything is good.
Now if you happen to get interrupted right now and leave the computer, and then come back 5 minutes from now, you're going to be completely freaked out because nothing you do in excel work. For example, If I try and choose cell E2 it doesn't work.
If I try and right click on this hyperlink, it doesn't work.
Why? Because I'm still in select objects mode.
I have to go back down to that white arrow and Uncheck it to get back to the regular mode where I can continue to use excel.
So it's a very cool tool.
Although, watch out because if you inadvertently leave it on, nothing will work in excel.
All right. So there we go.
We have a whole series of links.
Now tomorrow, I'm going to take a look at how we can take that data and actually extract the links from each cell and put it in a new column.
Thanks for stopping by. Will see you next time for another netcast from MrExcel.
