FlashFill is an Alternative to CONCATENATE and Text-to-Columns

Have you ever split one column in to many columns, or vice versa, combined many columns into a single column?  If so, you have likely relied on the text-to-columns feature to split a single column into many and the CONCATENATE function to join many columns into a single column.  Beginning with Excel 2013, Microsoft provides an alternative with the introduction of the FlashFill feature.  This post explores this feature.

Overview

The quick description of FlashFill is this: it watches you do the first row, and then it extends the pattern down.  Now, let’s fill in the details and work through a sample workbook.  First up, let’s split a single column into many columns.

Split One Column into Many Columns

In this first example, let’s say that you have a list of transactions with account codes, as shown below.

20130815d

 

The account codes were created with three segments, using the following logic:

XXX-YYYY-ZZZ where:

  • XXX is the business unit
  • YYYY is the account number
  • ZZZ is the department

If we want to generate subtotals by business unit, by account number, or by department, we need to split the account code into the individual segments.  Prior to Excel 2013, we could have tackled this task with formulas or with the text-to-columns feature.  The text-to-columns feature provides a wizard that allows you to identify the delimiter, in this case a dash, and then Excel would split the values into individual columns.  Formulas could also retrieve the individual segments, and common functions used to accomplish this task are LEFT, MID, RIGHT, LEN and FIND.  However, we have a new alternative to these approaches beginning with Excel 2013.  Let’s use the FlashFill feature here.

All we need to do is enter the first one, and then the FlashFill tries to detect the pattern.  It then extends the detected pattern down throughout the range.  In this case, we want to pull out the middle segment, the account number segment.  So, we simply set up a new column and enter the first one, as shown below.

20130815e

Then, we just select the first one, and extend our selection through the range, as shown below.

20130815f

 

Finally, click the FlashFill button, located in Excel 2013 on the Data ribbon tab.  The pattern is extended through the selected range, as shown below.

20130815g

In my experience, Excel does a good job detecting simple patterns.

Now, let’s go the other way and combine many columns into a single column.

Combine Many Columns into a Single Column

In this example, we have a list of employee names.  The names are exported from our HR system with the first name, middle name, and last name columns.  We need to combine them into a single column.

The original export is shown below.

20130815h

We need to combine them, so, we just enter the first one, and then select the whole range, as shown below.

20130815i

Click the FlashFill button, and Excel attempts to detect the pattern and fill it down, as shown below.

20130815j

In my experience, this approach is certainly easier and faster than text-to-columns or formulas.  For the most part, simple patterns are no problem for FlashFill.  However, complex patterns can be a challenge, and when it can’t complete the task, it is good to know we can continue to use our former approaches, text-to-column and formulas.

The file that was used to generate the screenshots is linked below for reference.

FlashFill.xlsx

 

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

3 Comments

  1. LuAnn Weimer on December 18, 2019 at 10:00 am

    Very Helpful

    I did find issues with names if they were not consistent. EX: John M Smith Becky Howell David D Gantley II

    Suggestions

  2. Daniel Ezuzu on July 27, 2022 at 9:16 am

    great work. very useful

  3. Paul L Verderber on September 14, 2023 at 4:46 pm

    Flash fill is handy, but one advantage of using formulas is that if the underlying data changes a formula will update whereas the flash-fill is static.

Leave a Comment