New Tool to clean data in Excel 2013

Excel contains a new tool that is brilliant to help clean data in Excel 2013. It is so brilliant that the only way is to show you what it can do.

It is called Flash Fill and what Excel does is it look at your existing data, looks at a sample of what you want, and then completes the rest of the column WITH NO FORMULA REQUIRED.

These and other useful tips and tricks are part of our live and online training courses.

Split a name and surname based on the upper and lower case letters

We showed how to do this recently using the CODE function. But watch below as the Flash Fill tool identifies that the name and surname are split with the capital letter on the surname and then does the rest for you.

For more on how to use Flash Fill see the YouTube video below

Add Capital Letters to names

Have you received a list of names but all in lower case? You can use a function like PROPER but watch as Flash Fill identifies the trend and completes the list.

Create a complex name from multiple columns

Do you need to take a persons first, second and surname and combine them into a first name, initial and surname? Flash Fill can do this. Note that where there is no second name Excel has left it blank. You can now enter what should happen in that case and Excel will apply that same rule to the rest

Split the name and surname

Split the name and surname in a cell into 2 different columns? We teach you how to use FIND, LEFT, RIGHT and MID to do this but watch Flash Fill do its thing

Create email addresses from a list

Create email addresses from a list of names based on the naming convention

Format phone numbers

Don’t like the format of your phone numbers but it is too much effort to retype them all. Flash Fill can do this

Change date formats from American to European or back

Have you been given dates in the incorrect way and Excel is not recognising them. Flash fill will fix this once is knows what you want.

Extract a date from report headings

Want to pull out a date from some text? Note that the dates are in different positions within the text but once Excel knows what you want it pulls out the key information.

Not Working? Give Flash Fill more information

If the fill does not make sense it means the Excel has not fully understood what you want. By providing another example Excel will be able to identify the trend and complete the list. Note below that after the first entry it thinks you only want the 1-9-2011. When you provide the second entry Excel understands what you want and completes the process. Give Excel more examples to make Flash Fill more accurate.

Warning

As with everything, you must check what the results look like, but Flash Fill should significantly reduce the amount of work you need to do.

Related

How to remove numbers from text cells in Excel

Using multiple characters as delimiters in Excel Text to Column