How to Split Text in Cells with Flash Fill in Excel

Bottom Line: Learn how to use the Flash Fill feature of Excel to clean up and extract data.

Skill Level: Beginner

Watch the Tutorial

Download the Excel Files

You can practice or follow along using the BEGIN file that I use in the video. I've also included the FINAL file that has the finished result.

Different Ways to Split Text

This is our fourth post exploring different ways to split columns. The example we're looking at is taking a Full Name column and breaking it into First and Last name columns.

So far, we've learned how to split names with Power Query, with the Text to Columns feature, and using formulas. These prompted some really great questions and suggestions. I'm particularly grateful to Wayne, Paul, and HG for their recommendation to use Flash Fill, which we're going to look at today.

If you've never used Flash Fill before, it's super easy.

Using Flash Fill

Flash Fill is an intuitive feature in Excel that's pretty cool. Essentially, Excel guesses what you are trying to do based on one or more entries that you give it as samples.

Let me show you a few examples of what I mean.

Starting with a column of full names, I simply type (or copy and paste) the first name for the first entry in the column.

Then, with any cell in the column selected, you can use Flash Fill to populate the rest of the cells. Flash Fill is an option on the Data tab. Or instead you can use the keyboard shortcut Ctrl + E.

Excel will fill the rest of the column with values that it thinks you want. In this case, it correctly pulls all of the first names out of the full name column. The same can be done with the last names as well.

Automatic Flash Fill As You Type

One feature I forgot to mention in the video is Flash Fill's ability to automatically fill the column with values. This can happen when you start typing the value to return in the second cell in the column.

After you start typing in the second cell, you might see a preview of the data below. You can simply hit Enter to accept all the changes and fill the column with values.

This automatic fill option for Flash Fill can be turned on/off by going to File > Options > Advanced > Editing Options > Automatically Flash Fill checkbox.

Another Example

Flash Fill can extract information based on other characters or delimiters as well. For example, If I have a list of email addresses and I simply want to pull out the domain portion of the address so I can see what email providers are most popular among my clients, I can use Flash Fill in the same way. If I type one example of just the domain portion of the email address, Flash Fill pulls everything after the @ symbol in the subsequent cells.

Something More Complex

Flash Fill is most accurate when everything in your column is relatively uniform, as in the examples above. As things get a little more complex, Flash Fill has more trouble figuring out exactly what you're looking for.

Here's an example of a list that has middle initials, hyphenated last names, multiple word last names, middle names, and titles. When I try to Flash Fill the last name, you can see that there are some incorrect results.

If you correct one or two of the entries and perform the Flash Fill operation again, Excel will attempt to “learn” from your changes and will potentially return different results. The above list has too many complexities for Flash Fill to completely get it right, but if you have a list that is a little less varied, a few iterations of Flash Fill might provide you with an accurate list—or at least be good enough that you only need to do some minor clean-up.

So if your list isn't perfect on the first try, give it a few more examples and then run Flash Fill again.

Flash Fill Pros and Cons

The biggest advantage of Flash Fill is obviously its ease of use. Type a few entries (or copy and paste them) and then click a button, and you could potentially have an entire column of accurate data immediately. Excel does all the work for you.

A major disadvantage, aside from Flash Fill not always being accurate for complex lists, is that it doesn't automatically update with changes. With the formula solution, changes to your split columns automatically occur when you adjust the source column. With Power Query, a simple refresh is required when changes are made. For this Flash Fill solution, we would need to repeat the original process when updates are made to the source list.

Flash Fill would also not be able to extract middle names from a list of full names, like Power Query can, unless all of the entries in the source column include a middle name.

Ways to Split Text

Here are the links to the other posts on ways to split text:

• How to Split Text in Cells Using Formulas
• Split Cells with Text to Columns in Excel
• How to Split Cells and Text in Excel with Power Query
• Split by Delimiter into Rows (and Columns) with Power Query

Conclusion

So, while Flash Fill is limited in being able to figure out more complex data sets, it remains a phenomenal resource to use for simpler and more uniform entries. It's super quick and intuitive and it's a great tool to have in your Excel toolbelt.

If you have any questions or suggestions, I'd love to hear them. You can leave them in the comments below.