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.
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.
The account codes were created with three segments, using the following logic:
- 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.
Then, we just select the first one, and extend our selection through the range, as shown below.
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.
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.
We need to combine them, so, we just enter the first one, and then select the whole range, as shown below.
Click the FlashFill button, and Excel attempts to detect the pattern and fill it down, as shown below.
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.
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
I did find issues with names if they were not consistent. EX: John M Smith Becky Howell David D Gantley II
great work. very useful