Did you know that Excel 2013 and 2016 have a built in feature to help you fill and format columns of info? It's called Flash Fill and it does just what it says it does. I love finding new tips and sharing them.
How long did it take you figure out how to separate names by using a long formula to find the "," character and then pull info from the left or right of that? Or better yet, the faster way of using Text to Columns to separate the text. Well, now there's an even quicker way.
Start off with a column of info, such as names and make sure the headers are formatted differently to help Excel know where to look for the patterns in data. In this case, I'm using Last Name, First Name. Start a new column to the right for First Name. Type the first one like you want it and hit Enter to go to the next line. Start typing the next one and you'll see a grey-out list of suggestions pop up. If you like it, hit Enter. Boom! Your column is already filled. Do the same for the last names.
If this is not working automatically for you, check Excel's options in the backstage view. Go to File> Options> Advanced and scroll to Editing Options. Check the box for "Automatically Flash Fill". You can also force it to work by typing the first entry and then hitting Ctrl+E.
You may have some rows that Excel doesn't quite know what do with. Yes, I just ended a sentence with a preposition. Maybe some will have initials or middle names. Just go to those cells and make the entry and the rest will fill in for you.
Try separating the raw data of 123121234 into 123-12-1234.
Phone numbers: 5551231234 to (555) 123-1234
Is your original data in ALL CAPS or all lower case? Type the first cell in the Proper Case and it will be corrected for you. ESTHER, POLLY becomes Polly Esther.
Have a mix of initials and middle names?
- JEST, SHIRLEY U. becomes Shirley U. | Jest
- brown, jim ed becomes Jim E. | Brown
As with most features in Excel, there are multiple ways to accomplish most tasks. You could format phone numbers, social security numbers and zip codes with the Special number format, but Flash Fill handles this without having to use the mouse or click through several dialog boxes. It's nice to have options.
Comments
Post a Comment