Excel data cleanup using Word!


Efficiency 365

Read the article, Answer the Quiz,
Win Prize (10 USD Amazon voucher)

Problem

One of the participants (Mr. Amjad) in a seminar I conducted asked me this question.
The scenario is common enough.

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

image

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.

image

Then choose Data tab – Flash Fill

image

Do the same thing for the name in column F. That’s it…

View original post 514 more words