Flash Fill in Excel is a new feature that was introduced in Excel 2013.

Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.

One of the cool uses of Flash Fill is convert your values into Excel dates automatically.

 

DOWNLOAD EXCEL WORKBOOK

 

To demonstrate the power of Excel’s Flash Fill, we will start off with this table of data where we need to convert to dates:

Flash Fill - Convert Text to Dates 01

 

STEP 1: Type 04-23-2016 as the first entry in the Date Format column.  

Flash Fill - Convert Text to Dates 02

 

STEP 2: We want the rest of the Text to be formatted this way, so in the second entry, type 12-30-2015.

(It is recommended to even type in a 3rd entry when dealing with dates, as there are many permutations and regional time formats!)

 

Notice that Excel did not auto-suggest to Flash Fill. There are times that this happens.

Flash Fill - Convert Text to Dates 03

Since Flash Fill did not start automatically when you are expecting for it to match your pattern, you can start it manually by highlighting the entire column you want it to fill.

Then click Data > Flash Fill or another alternative is to press Ctrl+E keyboard shortcut!

Flash Fill - Convert Text to Dates 04

Flash Fill - troubleshoot

 

STEP 3: You now have your data auto-populated using Flash Fill.  

What is very impressive is Excel was able to apply the same date format pattern to the rest of the table without the use of a single formula!

Flash Fill - Convert Text to Dates 05

flash-fill-convert-values-to-dates-v2

 

HELPFUL RESOURCE:

Oz_Podcast_Banner

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Find & Replace The Find & Replace feature or CTRL+H shortcut allows you to amend your data in seconds.  Imagine you had thousands of rows of data that was downloaded from an external system with the wrong date.  A simple CTRL+H will save you heaps of time!  See how below. DOWNLOAD WORKBO...
Split First & Last Name Using Text to Columns There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns. One way is to use the Power Query method, which is great if you have lots of data that gets added each day, ...
Show Pivot Table Values As a Running Total In If you want to track your sales on a monthly basis and see their Year to Date totals, then you can easily do this with the Running Totals In calculation. You can activate this by Right Clicking on your Pivot Table values and choosing Show Values As and Running Total In.  You c...
Show The Percent of Column Total With Excel Pivot ... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF COLUMN TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, ...