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

Watch on YouTube & Subscribe to our Channel

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.

Excel File Icon 2021 xlsx Split Names with Flash Fill – BEGIN.xlsx

Excel File Icon 2021 xlsx Split Names with Flash Fill – FINAL.xlsx

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.

Split text in cells into separate 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.

Flash fill type out sample of what file should contain

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.

Flash Fill Button 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.

Flash fill automatically populates a column

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.

Flash Fill Autofill Preview in Excel

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.

Flash fill extracts email address domains

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.

Flash Fill mistakes in complex scenarios

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 updates are not automatic

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.

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.

  • I’ve used Flash Fill to combine names from First/Middle/Last columns (the opposite of your example) by sorting the names into those WITH a middle name and those WITHOUT and running Flash Fill over each collection. I do recognise Flash Fill’s shortcomings but as a long-time user of EXCEL it still seems like magic to me!

    • Hi Anne,
      Thanks for sharing! That is a great idea to sort the data on the middle names and run it twice. It would still be a lot faster than doing it manually. And I agree it’s a pretty magical Excel feature.

      Thanks again and have a nice day! 🙂

  • I just cleaned 9000 lines of names and used text to columns, which was great, but I sure with I had known this! Thank you for sharing your knowledge.

    • Thanks, Debra! Yes, Flash Fill can be faster than Text to Columns. I’m happy to hear you now know both techniques. I think they each have their use cases.

      Thanks again and have a nice day! 🙂

  • Thanks for highlighting Flash Fill – there are always so many workarounds in Excel using arcane formulas and I’ve been going the long way to extra inner words, dates, or numbers from text strings. I was using “left”, “middle” and “right”. Wow was I wrong. I do not see any shortcomings with the feature, as Anne Brown-Robins, has mentioned. I really appreciate the clear graphics and downloadable files for practice. All of the downloads worked flawlessly and I will incorporate flash fill in my Excel files. Thanks!

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >