Usually, you grab text from Notepad or Email or a web page, paste to Excel, and it all stays in one column.
But…one day, late in the afternoon, the pasted data starts automatically parsing into separate columns!
Here is the deal… I bet you did a Text to Columns earlier today, where you split the data by a Comma or a Space.
Normally, Step 2 has a checkmark next to Tab. Not many people paste data with tabs, so you never noticed that Excel *always* splits pasted data by whatever is in step 2 of the Text to Columns Wizard. But, on a day where you do a text to columns and choose Comma or Space or Semi-colon…. well, on those days, any data copied from an external program and pasted to Excel will automatically parse.
To “fix” it: Close and re-open Excel. Or do a fake text to columns and change back to a Tab.
Also… every once in a while you might have to do a lot of pasting and then text to columns. On those days, doing one fake Text to Columns to get step 2 to have a comma can save you a lot of time!
So, you have this nice little =VLOOKUP(A2,$Z$2:$AB$99,3,False) formula. But in order to prevent the #N/A! error when something is not found, you used to have to do the VLOOKUP twice: =IF(ISNA(VLOOKUP(A2,$Z$2:$AB$99,3,False)),”Not Found”,VLOOKUP(A2,$Z$2:$AB$99,3,False)).
This is far simpler starting in Excel 2010:
Think about it. Let’s assume you are doing 1000 VLOOKUPs. There are 990 that are found and 10 that are not found. The old formula would end up doing 1990 VLOOKUPs. The new formula will do 1000 VLOOKUPs. It will be almost twice as fast.
I am taking my Power Excel seminar back on the road for 2014-2015, hitting many of my favorite cities from the past plus some new cities.
If you live near one of these Power Map markers, I would love to see you at a seminar this year.
For the complete tour schedule, visit http://www.mrexcel.com/pressappearances.shtml
MrExcel tour poster (c) 2007 by Hatch Show Print. Used with permission.
Have you found a great Excel feature that is buried three clicks deep? Make it always be 1 click away by adding it to the Quick Access Toolbar (QAT)
Adding to the QAT is simple: Right-click the icon and choose Add to Quick Access Toolbar.
EduPow is a start-up website with a mission to provide affordable learning.
They invite anyone with a passion about any topic to create a 1-hour video course and upload it to EduPow. Of course, I am passionate about Excel, so I have uploaded several Excel courses to EduPow.
EduPow’s mission is to make learning affordable, so all classes on EduPow are priced at just US $5! You might be thinking… are you crazy? Why would you sell a course for $5? Actually, I think that pricing is about right. Already, QUE sells my 7-8 hour Power Excel class for $39, which works out to about $5 an hour. Sometimes, you don’t need an entire course, so EduPow gives you access to a quick course about one specific topic.
To make things more fun, EduPow is offering me a chance to win a 1/2% stake in their company. That’s right… the course that sells the most between September 1 and September 15 wins an ownership stake in the company. If you want to help me out in this contest, check out my $5 course: Excel Formulas – The Dirty Dozen. Everyone who buys a course before September 15 is in a lucky drawing to win an iPad Mini.
And… why not consider adding your own course to EduPow? Everyone has a hobby where you can teach others. Pull out your cell phone video camera and teach something. It can be a fun way to share your knowledge and make a few bucks along the way.