Excel data cleanup with Flash Fill


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.

flash fill

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.

flash fill

Then choose Data tab – Flash Fill

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.

flash fill

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? Word Logo

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 tableimage
  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 optionimage
  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.image
  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.

Sample Word file for practice Download

How did this work?

The magic happened in that complex looking search criteria


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.

Character in Range


10 Responses

  1. Hi Sir, very useful, Regarding the QUIZ – In MS Word, File>Options> Proofing, Last Option – Hide Spelling Errors and Grammar Errors. (I used it some times) ; Thank you, Regards, Abhishek Chaudhary. abhishek.c@tatamotors.com

  2. The name consist of a single letter and a word.So for single letter there will be no error. If you have already added the words into your local dictionary then also no error. (this will not hold good for me 🙂 )


    You might have activated this two Hide spelling errors in this document only
    and/or Hide grammar errors in this document only (this will not affect othe documents)


  3. Hi Manas.
    Thanks for replying. The setting is – File – Options – Proofing – Hide spelling errors (and Hide Grammar errors). This setting is per document and more importantly, it travels with the document.

  4. Thanks Manas. These are NOT “Tips and Tricks”. Why not?
    Usually Tips and Tricks are of the type – this problem – this solution. That’s it. The concept is not explained. How it happened is never explained – why? Because, apparently, nobody is interested. Everyone wants quick and dirty solutions. Unfortunately, that is not called LEARNING. That is SPOON FEEDING.
    Specific problem may be solved. But with little extra knowledge, the benefit can be amplified. That is what I am trying to do.
    Problem – Solution – Concept – Further stimulus = Wholesome Learning 🙂

  5. Answer to quiz:
    go to> “Word Options” from top left Office Button
    >Select “Proofing” from the side menu
    > on right frame under “Exception for” you will find the current document’s name which is open
    > Select the check box “Hide spelling errors in this document only”
    Document will not show spelling errors with red wavy line below it.

    1. Hi Atul
      Thanks for replying. But the prize is already taken. I got the reply within 20 minutes of posting. See the comments section for details.
      Dr. Nitin

Queries | Comments | Suggestions | Wish list