Break Data Apart Using Flash Fill

December 14, 2021 - by Bill Jelen

Problem: Using LEFT, MID, RIGHT, FIND, and LEN makes my head hurt.

A series of part numbers in A2:A5 have three sections separated by hyphens. Complicated formulas are shown that use LEFT, MID, RIGHT, and FIND to isolate the three parts of the formula. Rather than explaining these formulas, the new Flash Fill will make this simple. Read on. — Figure 220. Formulas to split data, from a previous edition of this book.

Strategy: Excel 2013 introduced a new Flash Fill feature that simplifies this process.

Say you want to break out the left, center, or right portion of the account ID in column A. Add a heading above column B. Type a sample of what data you want to break out from column A. Select the blank cell under your sample. Press Ctrl+E to invoke Flash Fill. Excel will fill in the remaining values.

In Row 2, type the three sections of the part number from A2 into B2, C2, and D2. Go to the blank ceell in B3 and press Ctrl+E for Flash Fill. — Figure 221. You need both the heading and the first sample for Flash Fill to work.

Flash Fill will use any of the columns in the current region. Press Ctrl+E from C3 and then from D3 and you will fill all three columns.

Flash Fill breaks out the rest of the rows based on the example you typed in B2:D2. — Figure 222. After running Flash Fill in B3, C3, and D3.

Additional Details: Sometimes the first row of the data produces an ambigious example. For example, imagine if the part number was 999-999-999, Excel would not know if you wanted the first, second, or third segment. In this case, type an example in row 2, another example in row 3, and then Ctrl+E from B4.

Other times, there are two sets of rules that need to be applied. In the Figure below, you are trying to break out the first name from column A.

Column A has full names. I threw in every famous person I know with two first names: Jamie Lee Curtis, Sarah Jessica Parker, Lisa Marie Presley, and Mrs. Excel: Mary Ellen Jelen. Mixed in are other people with a single first name: Gypsy Biker, John Henry, Tom Joad, Mary Dove, Bobbie Jean. After typing Gypsy as the example and using Flash Fill, you get only the first word: Gypsy, John, Mary, Sherry, Tom, Mary, Bobbie, Jamie, Sarah, Lisa. Flash Fill is still active, waiting to see if you make a correction. — Figure 223. Type Gypsy, then press Ctrl+E from B3.

After applying Flash Fill, you can correct a later cell that represents the other rule. Type Mary Ellen in B4 and press Enter. Flash Fill will look for other examples of three names and fix those.

Make a correction in B4, typing Mary Ellen instead of Mary. When you press Enter, Excel reports that Flash Fill changed three other cells. The three actresses in B9:B11 now say Jamie Lee, Sarah Jessica, and Lisa Marie. — Figure 224. Correct one of the filled cells and Flash Fill will look for similar examples.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jennifer Lim-Tamkican on Unsplash