FlashFill is an amazing tool which helps you to concatenate, merge, or split text in cells without using a single formula. 😎😍😍
How it works
No more formulas, just type what you want to see into a cell and Excel will try to understand the rules. Sometimes, you need to fill more than 2 cells when rules are complex, but it will eventually work in any scenario.
In this example, you want column D to show the concatenation of the columns A, B & C. For the first row, you manually type Mr Thomas Smith into cell D2.
When you type into cell D3, starting with the letters Mrs, Excel automatically detects the rule and presents a suggestion in the cell and the cells below in grey 😲😃😍
Hit Enter and all the other cells are filled with the same rules 😎😎😎
Split the contain of one cell
When you have a lot of data in one column and you want to split it into several columns, FlashFill can help you with that, too. Here, you have a list of addresses. Unfortunately, each address is in only one cell.
To extract the State or the ZipCode, just manually type two or three rows of the ZipCode and the State column and Excel will suggest how to complete the column. If Excel does not make any suggestions, use the keyboard shortcut Ctrl+E to force Excel to use FlashFill.
Correct some mistakes
Now, if you want to extract the city, just type the first city name Concord into D2. Then, in D3, write the first letters or the next City name and that’s all.
But it doesn’t work everywhere 😱😱😱
For San Antonio it doesn’t work 😒 In fact, at this step, you have just extracted the last word of the city.
But you can correct that by editing the cell D4 and correct the mistake.
Hit Enter, and you can see that now the list of cities is correct — not only for San Antonio but also for San Francisco, San Andreas 😍😎😃