Over the past 25 years I have constantly been asked how to easily extract single words out of multiple words in a cell in Excel. This is normally because people receive imported data from external applications where, for example, people’s names are merged into a single. It used to be a pain to do this but Microsoft have developed this wonderful effect called Flash Fill. If you need to split up data you will absolutely love this.
The Flash Fill Tool
Here is a tiny list of people’s names that we are going to work on.
As you can see both first and second names are in one cell.
- Select the cell B2
- Type the first word in A2 which is the surname
- Do NOT press Enter to accept this information.
- Click on the Tick in the Formula Bar OR hold down the CTRL key and press Enter. Whichever way you prefer. Just as long as you remain in the cell you have just typed.
- Select the drop down arrow next to the Fill tool on the Home tab.
This list appears.
- Click on Flash Fill which is the last choice.
The result is that Excel extracts the first word, in this case the surname
- Now type the forename into the cell C2 and do the same as before. Accept the word and select Flash Fill.
Here is the result.
Notice that the word Name has also been extracted and placed above C2.
- Expand the column widths and there you have it.
More complex extraction
Here is a cell with more words and numbers.
What we need to extract is the country they live in.
- Type the first country you see in J2 into the cell K2
- Accept the entry and select Flash Fill.
Here is the result.
Because Germany is just one word you don’t get the result Great Britain just Britain for the second record. If on the other hand this had been the first record then you would get this result.
Flash Fill is great but it does only what you ask of it. It cannot think for itself and realise that some countries have two words or more to them. At lease it’s a start and a lot faster than ever before.
