Flash Fill
By Richard Rost

5 years ago

Quickly Extract Data From a Column in Excel

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this video, I'll show you how to use Flash Fill to quickly extract data from a column of information in Microsoft Excel.

Learn More!

Questions?

Please feel free to post your questions or comments below. Thanks.

Keywords

#msexcel, #microsoftexcel, #flashfill, flashfill, flash fill

Comments for Flash Fill

Age	Subject	From
5 years	New Fast Tips	Richard Rost

Subscribe to Flash Fill
Get notifications when this page is updated

Intro

In this video, we will learn how to use the Flash Fill feature in Microsoft Excel to quickly separate data from a single column into multiple columns, such as splitting city, state, and zip code from a list of addresses. I will show you how to use Flash Fill both from the menu and with the Control-E shortcut, as well as a tip for handling zip codes stored as text.

Transcript

Welcome to an Excel Fast Tip brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.

In today's video, we are going to learn about Flash Fill. You have a list of addresses with City, State, and Zip Code, and you want to separate the different parts. We can use Flash Fill to do this.

Click on cell B1 and type in Dallas. Press Enter. Now, come up and click on Data, and then under Data Tools, find Flash Fill. Notice Flash Fill is also Control-E. If you use it often, remember that keyboard shortcut. Click on Flash Fill, and now you will see all the cities have been separated.

Let's come over here to column C to type in Texas. Press Enter, then hit Control-E. Flash Fill knows what you are trying to pull out, and it pulls all the states out for you.

For the Zip Code, make sure you are formatted as Text first with a single quote. Then type 24060, Enter, Control-E, and get rid of those warnings by selecting the column. Click here, and then go to Ignore Error.

And there you go. That is Flash Fill. Want to learn more Excel? Visit my website for many more free classes, including an hour-long Excel beginner course.

Quiz

Q1. What is the purpose of Flash Fill in Excel as demonstrated in the video?
A. To automate the separation of combined data into individual parts
B. To add formatting to cells
C. To perform calculations across multiple cells
D. To filter data based on specific criteria

Q2. What is the keyboard shortcut for Flash Fill in Excel?
A. Control-A
B. Control-C
C. Control-E
D. Control-F

Q3. After typing the first value for City in cell B1, what should you do next to use Flash Fill?
A. Copy and paste the formula
B. Type all the city names manually
C. Click on Data, then Flash Fill under Data Tools
D. Use the sort function

Q4. When separating the Zip Code, why should you format the column as Text first?
A. To ensure numbers are displayed as currency
B. To maintain leading zeroes and prevent automatic number formatting
C. To perform calculations on the zip codes
D. To convert text to uppercase

Q5. What is a quick way to remove the warning indicators after using Flash Fill for Zip Codes?
A. Delete the entire column
B. Copy and paste the values to a new sheet
C. Select the column, click on the warning, and choose Ignore Error
D. Use the Find and Replace tool

Q6. Where can you find more free Excel classes as mentioned in the video?
A. Microsoft Office Online
B. ExcelLearningZone.com
C. Your local library
D. YouTube only

Answers: 1-A; 2-C; 3-C; 4-B; 5-C; 6-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone is all about using Flash Fill in Excel to quickly separate data within a single column. Suppose you have a column containing full addresses, and you want to split these into separate columns for City, State, and Zip Code. Flash Fill can help accomplish this task with minimal effort.

Start by entering the city for the first record in the appropriate cell. After entering this example, you can use the Flash Fill feature, which can be found on the Data tab under Data Tools. If you find yourself using this feature frequently, keep in mind that the keyboard shortcut is Control-E. Using Flash Fill, Excel recognizes the pattern based on your entry and automatically fills the rest of the column with the cities extracted from your address data.

You can repeat this process for the State column. Type in the state corresponding to the first entry, press Enter, and then use the Control-E shortcut. Flash Fill will extract the state from every address for you.

For the Zip Code column, it is a good idea to ensure that the cells are formatted as Text so that Excel will preserve any leading zeros in your Zip Codes. You can do this by starting with a single quote before entering the number. After entering the first Zip Code, press Enter, then use Control-E again. You might see some warnings about the data format. To resolve them, select the column and choose to ignore the error from the warning menu.

That covers how to use Flash Fill to separate City, State, and Zip Code data in Excel. If you are interested in learning more, visit my website for many additional free Excel classes, including a full beginner-level course.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Using Flash Fill to separate data in Excel
Extracting city names from address data
Extracting state names from address data
Extracting zip codes and formatting as text
Using Control-E keyboard shortcut for Flash Fill
Resolving number stored as text warnings in Excel

Links

Learn More!

Questions?

Keywords