Flash Fill Times

Flash Fill is a marvelous feature that is designed to fill values down based on an example in an adjacent column. That is, you have a column of values and then you enter an example of a derived value in an adjacent column. Flash Fill attempts to recognize the pattern from your example and then fill that pattern down. This is difficult to visualize with words, so, let’s walk through an example that converts a column of text strings into valid times.

Video

Narrative

I needed to import a bunch of events into my calendar, and the data source showed the event times in a format that wasn’t able to be imported. For example, the first few rows in the data source looked something like this:

In order to import the events into my calendar, I needed to split the Time column into Start and End columns, like this:


Excel provides many options to accomplish this, for example, I could have used formulas or Power Query. But, I just wanted something fast and easy to use. So, I used Flash Fill.

Let’s walk through the steps together.

Details

We’ll walk through these steps:

  • Get Time Start
  • Get Time End

Let’s do it.

Get Time Start

The first step was to get the Time Start values from the Time column. At this point, the worksheet looked like this:

The idea with Flash Fill is that you enter an example of the desired values in the adjacent column  …

… and then you click the Data > Flash Fill command (or Ctrl+E)  to fill that pattern down.

When I did, I got some unexpected results:

When I inspected the Time column more closely, I noticed that the time was expressed using a text string of a dot and no space (1.00PM) instead of time format with a colon and a space (1:00 PM). As such, Flash Fill didn’t detect my intended pattern.

I was asking Flash Fill to extract the start time from the Time column, plus convert the text representation of the start time into a valid time data type. One solution is to break the sequence down into smaller incremental steps. That way, we only ask Flash Fill to concentrate on one step (transformation) at a time.

But, we can also assist Flash Fill by providing additional examples. So, I gave Flash Fill two examples instead of one:

This extra example is exactly what Flash Fill needed to understand my desired results. When I clicked the Flash Fill command this time … bam:

All that remained was to populate the Time End column.

Get Time End

Once again, I entered an example value:

And Flash Filled it down:

Again, it didn’t quite understand my desired pattern as it gave me 1:00 AM for the second row instead of 1:00 PM as expected. So, I entered an additional example:

And this time … bam, it worked:

As you can see, Flash Fill provided an easy way to split the Time column into Start and End columns, including converting them into time values. Brilliant!

Conclusion

Flash Fill is a wonderful feature of Excel that often provides a quick way to transform data columns without the need of something fancy like formulas or Power Query. If this were a recurring workbook, I’d probably take the time to automate this process with Power Query. However, for a one-time project, Flash Fill is a great option!

If you have any other fun Flash Fill tips, please share by posting a comment below … thanks!

Sample File: FlashFillTimes.xlsx

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

1 comment:

  1. Bill
    Reply

    Jeff,
    When I copy the time column without the date column and paste into another area of the spreadsheet, the flash fill works for the start time. For the end time, all numbers were correct, but it missed two of the AM/PM’s.
    Have you noticed that flash fill will “fill” even if you start with a cell in the middle of the group?

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.