Data analysis of any sort requires cleaning and formatting the data.

Predominantly, Microsoft Excel spreadsheet can be used for that matter. The source of data could be from multiple upstream systems! It’s highly unlikely that you would just get the data ready for further processing.

Let’s take a hypothetical example:

A fashion based e-commerce startup wants to identify which top 3 cities in a specific country has returned back the maximum products to their retailers. The company then might be interested to scrutinize the problems faced by its customers, and takes key decisions to minimize the returns or strengthens the returns policy to prevent the losses incurred by the same.

The returns team of that company maintains one relevant field by the name: “Address”. In the excel sheet, it would be a manual and repetitive task to extract the City/State/Pincode from the Address. Of course, one can use the combination of MID, FIND kind of formulas to extract what we want to an extent. Well, there’s still a better way in Microsoft Excel 2013 and above versions.

It’s called “Flash Fill” concept designed by Dr. Sumit Gulwani, Microsoft Researcher. This is a machine learning algorithm and discovers patterns based on a couple of data examples and populates the remaining data using what it had learned! This is a great deal of time saver for many cases. I’ll highlight an example below.

Using the available Address, we can now extract County/City/State/Pincode using Flash Fill feature.

1. Create a new field/variable and name it. I created “County” for my requirement.
2. I just typed three records manually such as Orleans, Livingston, Gloucester.
3. Then, I highlighted these three and dragged the text until the end of the records. You can see below that it just replicated the three words repeatedly.
4. At the end of this screenshot, you can see a tab that appeared to enable you to choose few more options.
5. Click “Flash Fill” and see the magic for yourself :). It has identified the pattern that I’m interested to extract only the County information from the Address field. You can similarly try to extract other key info such as State, Pincode.

In certain cases, the Flash Fill automatically pops-up and recommends while you type the sample data as per below.

You can apply Flash Fill to format your number such as Telephone number, Social Security Number etc. to name a few.

A couple of tips:

1. If it fails to identify pattern in your case, educate it by typing few more examples for “Flash Fill” to learn from it. Usually, I type 2 or 3 examples and the algorithm picks up thereafter for the remaining data.
2. In the above example, I had a separator such as comma to differentiate the county, state, pincode info in the Address field. So, it became pretty easier for “Flash Fill”.  Alternatively, you can iterate few more times to clean the data as per your wish.