For this lesson you need to open the sample file: Phone Book-1, from your Sample Files folder.
This worksheet has some examples of international telephone numbers. The first telephone number, for Books A Million, is a UK telephone number. And the second, for Maltese Books, is a Maltese telephone number.
International telephone numbers follow a standard format.
First of all comes the Country Code, (sometimes called the International Dialing Code). This is shown as a plus symbol followed by one or more numbers.
Next comes the NDD, or the National Direct Dialling Prefix. That's the number shown in brackets. It's zero for all of the phone numbers in this example. This is the access code used to make a call within the relevant country, but it's omitted when calling from outside the country.
Next comes the Area Code. This is the numbers after the closing bracket, but before the hyphen.
And finally comes the Telephone Number itself.
In this lesson we'll use a different technique with Flash Fill to split the Telephone Numbers into the Country Code, Area Code, and Phone Number. In Excel versions prior to Excel 2013, you'd have had to use some very complex formulas in order to split this type of text. But with the new Flash Fill feature, you can achieve the same result in seconds.
First of all, let's type a Country Code into cell C4. Now if you simply type: +44 you'll have a problem, because when you press the Enter key, Excel interprets this as a positive number of the value: 44.
But we want to actually show the plus symbol. So we want Excel to regard: +44, as being text and not as being a number. To do that, you need to press the Apostrophe key on your computer, followed by, +44. And when you do this, +44 is interpreted as text.
Notice that Excel's placed a green triangle in the top left corner of the cell. Excel thinks you might have made an error, but of course the value's fine. If you want to remove the green triangle, use the method you learned in Lesson 2-4.
Now I want to Flash Fill all of the country codes down the column. But automatic Flash Fill can't work with this data. It's too complex for Automatic Flash Fill to figure out. So if I was to type: ' + 356, you won't see any Flash fill entries appear beneath.
Instead we'll invoke a manual Flash Fill. To do that, you need to make sure the active cell is somewhere in column C. And then click Home on the Ribbon, in the Editing group, Fill, and then Flash Fill. And you can see that I've now extracted all of the country codes.
Now let's look at the Area Code. That's the number 113 in the case of Books A Million. So I need to do exactly the same thing. Type an apostrophe to tell Excel that 113 should be regarded as text and not a number, then type: 113, and press the Enter key.
Now I could Flash Fill in the same way I did last time by clicking Home on the Ribbon, Editing group, Fill, and Flash Fill. But notice that there's also a shortcut key for Flash Fill. You can see it on the tip beneath the menu: Flash Fill (Ctrl+E).
So let's use a Shortcut key instead. I hold down Control on the keyboard and press the E key, and that's worked just fine.
Now let's look at the phone number itself. Once again I need that leading apostrophe to tell Excel to treat the number as text. Otherwise it would do things like strip a leading zero off a number beginning with zero. And I'll type the number: 4960227.
Now I could use either of the two previous techniques. I could click Home tab on the Ribbon, Editing group, Fill, and Flash Fill. Or I could use the Control+E Shortcut key. But I'm going to use a third technique in order to Flash Fill the value in cell E4.
I'll click on cell E4 and I'll use AutoFill. To use AutoFill I hover over the spot in the bottom right hand corner and then drag down to the end of the range.
Now the numbers I'm seeing now are nowhere near what I want, because AutoFill has used its default behaviour and just provided sequential numbers. So I'll click the Smart Tag and I'll select Flash Fill, (the last option in the list). When I click Flash Fill, all of the numbers are extracted.
As mentioned at the beginning of this lesson, in Excel versions prior to 2013 (when the Flash Fill feature was introduced), this problem would be extremely complex. You can see how Flash Fill has made it simple, and we'll be seeing some even more amazing things Flash Fill can do in the next two lessons.
But for the moment all that remains is to save your work. And I'm going to save with the new name: Phone Book-2, and as usual I'll save to the folder above my Sample Files folder.
I click the Save button and you've now completed Lesson 2-19: Use Manual Flash Fill to Split Text.