Excel data cleanup with Flash Fill

Problem

One of the participants (Mr. Amjad) in a seminar I conducted asked me this question. The scenario is common enough. But the solution is not even noticed – it is called Flash Fill. Instead, we struggle to solve it manually (and inefficiently).

There is data in one column which contains name followed by amount. We want to split the amount.

Doing it using a formula is tricky. Because in some cases there are two spaces, in some cases there is only one space. And may be if the data is large, there could be three spaces as well.

Easy solution: Excel 2013 – Flash Fill

This is an amazing new feature. I will cover it in detail in a separate article. However, here is the way it works.

In column E, specify what you want. Just type the value. NO formula.

Then choose Data tab – Flash Fill

Do the same thing for the name in column F. That’s it. Excel has an amazing pattern matching engine which does the job for you.

What if you don’t have Excel 2013? Use Word!

Then you will have to resort to complex formulas or VBA. But not really. We often get too focused on the product and forget that Office has other tools as well.

Remember. Excel is great with Numbers and Dates. But not text.

Who is the text expert?

In fact what Excel 2013 Flash Fill does is technically called pattern matching (Regular Experssions). But Word had this feature since many years.

Let us see how to solve complex Excel data handling problems in Word.

1. Copy the column data and paste it in Word
2. It will be pasted as a table. But the table borders will not be visible.
3. Click inside the pasted data, you will see the Table Tools tab
4. Click the Layout tab and choose View Gridlines
   Now you can clearly see the table
5. We want to select only the numbers
6. Press Ctrl H to open the Find – Replace Dialog
7. We don’t want to replace anything so click the Find tab
8. In the Find textbox, type this exactly as shown here [0-9]{1,}
   
9. Click the More>> button and choose Use Wildcards option
10. Now try Find Next multiple times. Notice that it is selecting all the numbers one by one
11. But we want to select all numbers. So open the Find In dropdown and choose Main Document
12. Now all the numbers will be selected. Close the Find dialog.
13. Press CTRL X to cut the numbers and put them in clipboard
14. Only the names are left in the first column.
15. Now add a new column.
16. Select the second column
17. Press CTRL V to paste the numbers
18. Now select the whole table and paste it into Excel
19. Job done!

For the sake of completeness, the names column will have an extra space at the end. Add a new column and use the TRIM function to remove the trailing spaces if required.

How did this work?

The magic happened in that complex looking search criteria

[0-9]{1,}

Wildcard option means do not search what is typed literally. Try to interpret it.

[0-9] Means search for any number between the given range. But that would select only one digit.

{1,} Means that Word should search for one or more instances of number. That is how it selects the entire number

Want to know more?

Click in the Find textbox in Word, enable Wildcards and click the Special button.

You will see the entire syntax of what is possible. This is infinitely powerful and easy to use once you understand the concept. Try it out.