AccDox Shortener


Using the Flash Fill function to automatically complete data in Excel

I once had to split an Excel column with addresses: Region, municipality, village, etc. they were all in one column. I needed to separate them into separate columns for easier sorting or filtering. That's how I came across Excel's function: Flash Fill .

Flash Fill is a function to automatically fill your data when it senses a pattern from the previous column. For example, you can use Flash Fill to separate first and last names from one column or combine first and last names from two different columns or as in my case addresses.

Note: Flash Fill is only available in Excel 2013 and later.

Let's start with the following example:

Let's say column A contains a long list of three beneficiary names and you want to separate them into separate columns:

  • column B - first name;
  • column C – surname;
  • column D – last name.

1. Column B – first name;

1.1. Copy the entire first row from column A to column B.

Example: Ivan Asenov Yordanov;

1.2. In the copied row of column B, delete the first and last name.

Example: to remain alone: Ivan;

1.3. Highlight the first row and go to Fill and select Flash Fill.

2. Column C – surname;

2.1. Copy the entire first row from column A to column C again.

Example: Ivan Asenov Yordanov;

2.2. In the copied row of column C, delete the first and last name.

Example: to remain alone: Asenov;

2.3. Highlight the first row of column C again from Fill select Flash Fill.


3. Column D – surname;

3.1. As in point 1.1. and 2.1., copy the entire first row from column A to column D.

Example: Ivan Asenov Yordanov;

3.2. In the copied row of column D, delete the first and last name.

Example: Yordanov;

3.3. Highlight the first row of column D and again from Fill select Flash Fill.

We now have people's names in separate columns.

Where is Flash Fill in Excel?

Answer: Top right of the Home menu. I have not met anywhere else.

If it's not there from the Data menu: Flash Fill

The Fill button is located in the Home menu, under the AutoSum button /Excel's most used function/!

The Flash Fill button is in Fill!


Note: Flash Fill is only available in Excel 2013 and later. If Flash Fill does not generate a preview or cannot find any model or is not included.

You can go to Data > Flash Fill to start it manually or press Ctrl+E.


If it still isn't on, to turn Flash Fill on, go to: File > Options > Advanced > Editing Options > check the Automatic Flash Fill box. File > Options > Advanced > Editing Options > check the Automatically Flash Fill box.

And finally, if you have columns with HTML tags (links for example) in the Excel table and you want to remove them. Very easy: Ctrl + H in the column where the links are and enter in the Find what field: <*> , and in the Replace whit field leave blank or one space. And now the buttons Replace all "Replace all" or one by one Replace "Ground" are optionally pressed, the html tags disappear ....


Happy testing and using Flash Fill!


Comments

No Comments To Display

Add Comment

9 + 6 = ?