• https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
• https://accdox.com/shkmw

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

Flash Fill is a feature 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 versions.

Let's start with the following example:

Assume 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 - surname.

1. Column B - first name;

1.1 Copy the entire first row of column A into column B.

Example: Ivan Asenov Yordanov;

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

Example: leave only: Ivan;

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

2. Column C - the last name;

2.1. Copy the entire first row of column A into column C again.

Example: Ivan Asenov Yordanov;

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

Example: leave only: Asenov;

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

3. Column D - last name;

3.1. As in 1.1 and 2.1, copy the entire first row of column A into column D.

Example: Ivan Asenov Yordanov;

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

Example: Yordanov;

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

We now have the names of the people in separate columns.

Where is Flash Fill located in Excel?

Answer. I haven't seen it anywhere else.

If not there from the Data menu: Flash Fill

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

The Flash Fill button is under Fill!

Note: Flash Fill is only available in Excel 2013 and newer. If Flash Fill does not generate a preview or cannot find a pattern or is not turned on.

You can go to Data to run Flash Fill manually, or press Ctrl+E.

If it's still not on, to turn on Flash Fill, go to File > Options > Advanced > Editing Options > check the Automatically Flash Fill checkbox. File > Options > Advanced > Editing Options > check the Automatically Flash Fill checkbox.

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 type in the Find what "Find" field: <*> and in the Replace whit "Replace" field leave blank or single space. And now optionally click Replace all "Replace all" or one by one Replace "Replace", html tags disappear ....

Enjoy testing and using Flash Fill!