From the course: Excel: Managing and Analyzing Data

Splitting columns with Flash Fill - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial Buy for my team

Splitting columns with Flash Fill

“

- [Instructor] Here's a common situation. We've got data out of a PDF or we've pasted it in from a webpage or pasted it in from an email, and we've got all of these records that are all in one column. They're in their own rows, but look at this. This car is a Coupe, manual transmission, orange color, 80,000 miles, and it is a 2012 vehicle. Let's say, we want to extract the style and the mileage so that we can sort by those, but the rest of it can stay together. We don't need to split this all out. But also notice something about this data. For row 10, there is a space where the color should be, also rows 13 and 14. It's important to know about those because for one thing, by having those spaces, we know that our data is consistent. Had there not been that blank space, then we'd have a different kind of a problem to deal with. But with the data being consistent, we can extract what we need. Here we go. Coupe. Go down, SUV. There's ghosting where flash field is asking, is this what you are trying to do? I'm going to check a few rows like row 16 says Sedan in column B. And over in C, the ghosting effect show Sedan. Yes, row 11, Sedan, Sedan. Row nine, Coupe, Coupe. I'm going to hit enter. Those are the styles extracted. Now let's get the mileage out. 80055 10... Oh, look at the ghosting. I'm going to hit enter. There we have it. The mileage is extracted and in two separate columns, so we can use those values to sort and filter. But now flash fill is not dynamic like formulas. I'm going to scroll down. Here is more data. Now what I'm going to do with this highlighted, drag it into place. Now, I have to grab one column's worth of data and include the new data. Now, in the data tab of the ribbon, I'm going to go to flash fill. Success. It retrieved the style. Now, let's retrieve the mileage. Boom, there it is. Flash fill, you got to know about it. Okay, now let's go over here. Flash fill two. I want to show you this to warn you. Always, I want to warn you to make sure you are safe. We want to extract the last names. So here's Flanders, and then here is St. Scott. Okay, there was some ghosting, but it went away. I'm going to hit enter. Now, I'm going to highlight this much just to see, just to test. I'm still in the data tab. Go to flash fill. Okay? Hmm. Go ahead and do flash fill across the entire column of data flash fill. What do we get? Okay, we got van de Berg. Okay. Brown-Russo, good. Hodges Beck, that's a question. Is Hodges a middle name or is it part of the last name? We don't know. Up here, Paula-Jean Greenfield. Paula Jean is part of the first name, so Greenfield should be in this cell, not Jean Greenfield. Paul Bishop. The name is likely Sean Paul, or Sean, and then Paul as a middle name. Or, it could be the last name of Paul Bishop, but that is a warning about flash fill. It can be very helpful if you have really consistent data, or if your data is inconsistent, you have to be careful about using flash fill.

Contents