When working in Excel, the data is not always numbers, but text. While numeric data is great for calculations, when it comes to text data, you may need to know how to manipulate it to produce the results. It could be as simple as extracting (aka “parsing”) data or combining (aka “concatenating”) data.
While there are TEXT functions, such as LEN, CONCATENATE, RIGHT and LEFT, that are very useful and powerful, knowing how to use those functions is not always common knowledge, or the syntax for the formulas involving multiple nested functions (a function within a function) is quite complex, and even cumbersome.
Fortunately, the Flash Fill feature helps you to extract or combine pieces of embedded data, to produce the results.
Scenario:
In our example, there are 17 rows of employee data, that have 4 individual pieces of data all combined together into a single string (a single piece of data in a cell). Your objective is to “parse” the data into several individual columns, and also create a new one – Last Name, First Name, Department, and a new Email Address column. Use the Flash Fill feature to help you complete all the individual columns, including the last Email Address column.
** Dates cannot be extracted using Flash Fill.
FLASH FILL STEPS:
STEP 1. In Cell B4, begin typing the Last Name of the employee (“Kling” in this example), and press the ENTER key.
STEP 2. In Cell B5, begin typing the next last name “Willis”, and Excel brings up a “gray” list of names suggested for the remainder of the employees (Excel is essentially asking if you want the “Flash Fill” to complete the rest of the last names. The more unique the last names, the quicker Flash Fill will recognize the Pattern of all the Last Names. Sometimes, you may need type a few entries before Excel recognizes and suggests the pattern.
STEP 3. Press the ENTER key (to complete the list of Last Names).
STEP 4. Repeat the steps 1 to 3 for First Name and Department:
- Type the first name “Sara” in Cell C4, press enter; Begin typing the first name “Sean” in cell C5, and when you see the “Grey” suggestions, press the ENTER key to complete.
- Type the department name “Sales” in cells D4 to D7 manually, as they are not unique. When you begin typing the next department name “Development” in cell D8, Excel recognizes the pattern and suggests the gray Flash Fill pattern completion; press the ENTER key to complete.
Flash Fill will not pickup dates. You have to use a TRIM and RIGHT text functions for extracting the date from the right side of the data. The RIGHT functions extracts specified number of characters from the right, the TRIM function removes additional Spaces. Text functions are taught in Excel Level 2 Intermediate.
STEP 5. Using the TRIM and RIGHT functions to Extract Date from the Right side of Employee Data:
- Type “=TRIM(RIGHT(A4,11))” in Cell E4 and press ENTER.
- Copy down the Formula into the other cells below.
STEP 6. Continue using Flash Fill to complete the Email Addresses:
- Type the first email address in cell F4 manually, “sara.kling@duke.edu”, for example
- When you begin typing the next email address “sean.willis@duke.edu”, Excel Flash Fill recognizes the pattern; press ENTER to complete the email addresses
** Flash Fill, Text To Columns, and Text Functions/Formulas are the most common methods for Extracting and Combining data.