MrExcel.com - Your One Stop for Excel Tips & Solutions

Data Cleansing with Flash Fill

November 09, 2022 - by

Data Cleansing with Flash Fill

Problem: I need to join text or break apart text in Excel 2013.

Strategy: Use the new Flash Fill feature. Say that you have First Name in column A, Last Name in column B. You want first initial, a period, and a last name in column C.

  • 1. You must type a heading in C1 first or Flash Fill will not work.

  • 2. Type the pattern in C2: M. Henderson.

  • 3. Type the first letter in C3. As soon as you type J, Flash Fill will draw the proposed data in grey.

  • 4. Press Enter to accept the flash fill.

You type M. Henderson in C2. Then start to type J in C3. Flash Fill leaps into action, offering to finish C3 as J. Moore and then all of the remaining data in column C is offered in a light grey font. All you have to do to accept it is press Enter.
Figure 815. Flash Fill will save people hours of needless typing.

Gotcha: Flash Fill provides a static solution. If column A later changes, column C won’t update.

Problem: I saw Flash Fill propose a solution, but I did not hit the correct key, and now it is all gone.

Strategy: Finish typing the second entry. Go to the blank cell below your first two entries. Press Ctrl+E or select Data, Flash Fill. The data will come back.

This article is an excerpt from Power Excel With MrExcel

Title photo by Flash Dantz on Unsplash