![]() ![]() You won’t trust yourself to re-type the SSN correctly. Now, if you’re paranoid like me, then at step 2. Start typing the second SSN value: 2 At this point Flash Fill recognizes what you’re doing and makes additional suggestions: If you like the suggestions (in this case, I do!), just hit ENTER to accept them.Type the first social security number in B2, with hyphens: 4.Make sure the inserted column is wide enough to display the formatted value (80 pixels should be enough this isn’t required to use Flash Fill, but it looks better that way).Notice below that as you start typing the formatted SSN, Excel will help you complete the column based on the value you entered in the prior row. Step 1: Format social security numbers like this: 1 Insert a new column between SSN and Last Name and title it “ SSN”. But it also helps Excel to understand that the top row is a header row so that Flash Fill won’t use it as part of your examples when generating pattern rules. Note: I used bold formatting on the top row so they stand out. I used feature, using “comma” as the separator. ![]() ![]() After pasting the data into Excel, you’ll need to split it out into separate columns. I updated a few of the values from the original data set to make things more interesting, you can get it. Go to the next cell of the column and start typing the next phone number. Type the right format of the first phone number in a new column (e.g. If you have a large amount of data which needs to be entered, the new Flash Fill feature in Excel 2013 will help you. Here’s what Flash Fill looks like in action: Let’s work through the examples in my, but using Flash Fill as the solution. Now you can do it all by providing a few examples. Now, with Flash Fill, you can do it all without formulas. The problem is that the solutions relied heavily on using formulas, which can feel quite complex and intimidating for people not as familiar with Excel. Several years ago, I wrote a demonstrating how to do string manipulations like the above examples. Today I’m proud to be writing about a new feature in Excel 2013 called Flash Fill that makes all of those tasks very simple for even the most novice user. Because the data came from a database, I often had to make sure the names had Proper Casing (e.g. I had to change the format & display of dates, social security numbers, and dollar amounts. Sometimes I only needed first name initials, put together with the last name. I would join first names and last names together into a single column of text. I would extract first names, last names, and email addresses from chunks of text. Before coming to Microsoft I spent a good deal of time messing around with data. To try out this new feature download the Office Preview. This post is brought to you by Chad Rothschiller a Program Manager on the Excel team. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |