How to use Flash Fill to parse characters across multiple columns in Microsoft Excel

You might know how to use Microsoft Flash Fill to create new content, but you might not know that you can use this feature to parse characters across several rows.

Most of us have had Microsoft Excel data arrive in a structure that doesn’t work for us. For instance, you might receive a sheet of names where the first and last names are in the same cell. That type of data is difficult to work with. For instance, how would you sort that list by the last names? When you run into this type of problem, use Excel’s Flash Fill tool to restructure the data.

In this tutorial, I’ll show you how to use Flash Fill in an unusual situation. Specifically, I’ll show you how to parse characters from a single value into three columns. You might not realize that Flash Fill can do this, but it can.

SEE: Windows, Linux and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use Flash Fill in earlier versions down through Excel 2013. Excel for the web doesn’t support Flash Fill.

What is Flash Fill in Excel?

Excel’s Flash Fill tool provides a quick way to enter new data or clean up existing data. What used to require functions can now be achieved quickly with only a few keystrokes. This tool looks for a pattern and then applies it to the remaining values. To illustrate, let’s look at a quick example.

Figure A shows a simple sheet with my name in various patterns. Now let’s suppose that we need a column that contains only the last name from each entry. Before Flash Fill, you might have used a combination of string functions: RIGHT(), FIND() and so on. The necessary expression is quite complex.

Figure A

To trigger Flash Fill, enter the value you want for the first record. In this case, enter Harkins in C2. Next, select C3 and press Ctrl + E to see if Excel has figured out the pattern. In this simple case, it has, as shown in Figure B. As soon as you press Ctrl + E, Flash Fill completes the pattern for the remaining cells.

Figure B

The entries in C5, C7 and C9 aren’t correct, but that’s because the data isn’t consistent, and I wanted you to see that Flash Fill will also point out inconsistent data.

How to parse entries into multiple columns in Excel

With a simple example behind us, it’s time to look at a more complex requirement that you might not think Flash Fill can handle but it can. Figure C shows a list of entries composed of a variety of characters. Some entries begin with four numeric characters and end with six uppercase alphabetical characters, with a backslash character  in between.

Figure C

The first step is to create the pattern by entering the first three values manually in row 2. Next, select C3 and press Ctrl + E to see if Excel can determine the pattern. As you can see in Figure D, it did.

Figure D

Next, select D3 and press Ctrl + E. Then, select E3 and press Ctrl + E. As shown in Figure E, Flash Fill is able to fill in all three columns.

Figure E

Flash Fill got it right for the first three values but not the last four. Flash Fill isn’t the problem; the inconsistent data is the problem.

How to fix inconsistent data in Excel

Flash Fill is a great way to find inconsistent data. You can fix these problems by correcting the original values as follows:

• The value in B5 is missing a numeric digit, so enter a new digit to create 1943.
• The value in B6 has five numeric digits and is missing an alphabetical character. Enter 1578/DELBTF instead.
• The value in B7 is missing the / character. Enter 9995/HWWLVL.
• The value in B8 has a space instead of the / character. Enter 3447/LVLDEF.

After correcting the original values in column B, run Flash Fill again. First, delete everything in C3:D8 and run Flash Fill in C3, D3 and E3, to return the corrected set shown in Figure F. Even with the mistakes, Flash Fill took only a few minutes to parse the original values across three columns. For better or worse, when this happens, a manual fix is the only way to correct the original data. If Flash Fill exposes a pattern of inconsistencies, you might be able to fix those with a Replace task.

Figure F

Flash Fill is a great tool that interprets a pattern and applies it to data to quickly create new data, whether in one column or across several columns. When the results aren’t correct, either Flash Fill misinterpreted the pattern or the original data is inconsistent. If it’s the former, enter a few more entries to help Flash Fill determine the correct pattern. When it’s the latter, correct the original values.