Learn Excel - Cleaning Data Pasted from the Web - Podcast #1781

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Sep 2, 2013.
Grab some data from the web, paste to Excel, and you get all sorts of things that you did not expect. Today, there were tiny icons, hyperlinks, columns of the wrong width. Today's episode shows Remove All Hyperlinks and a great way to get rid of the pasted icons in a single command.
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1781: Clean Data Pasted From The Web.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. We’re back from nice week at the beach, ready to roll some more podcasts here. I’m starting out on Wikipedia, grabbed some information here, selected that, CONTROL+C to copy, switch over to Excel, CONTROL+V to paste, and, boy, they bring all kinds of things that I didn't expect.
Alright. First new trick. I’m going to right-click, and at the very bottom, we now have something called REMOVE HYPERLINKS. This is new, a great way to get rid of all of the hyperlinks in a range, starting in Excel 2010. It's only there when you have a range selected with multiple hyperlinks. Next, these little icons, the little flags, that are appearing here, isn't that nice, but I didn't want that at all. So, I'm going to go to FIND & SELECT, and there's something called SELECT OBJECTS.
This is a great way to get rid of all of the graphics in a range [ unintelligible – 00:55 ].
So, now I have that icon there. Just draw a big box around all of those graphics. It selects them all at once, hit the DELETE key, and they are gone.
See, now, I just tried to click to select a cell, not working. You have to press the ESCAPE key to exit that mode. Next, we'll do CONTROL+1 and I always turn off WRAP TEXT.
So, 2 clicks there, and also if there were any, we would want to uncheck MERGE CELLS, click OK, and now a simple ALT+O C A – format column autofit selection – will make everything look good.
Alright. Well, hey, there you have it, a couple of cool tricks. First, remove all hyperlinks, and then second the FIND & SELECT, SELECT OBJECTS to select all those little icons that get pasted in.
Well, hey. I want to thank for stopping by.
We'll see you next time from another netcast from MrExcel.
 

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top