In Excel 2013 and later, you can extract text without a formula using Flash Fill.
What is Flash Fill?
Flash Fill is a tool integrated into Excel, that runs in the background continuously.
You don't have to click anywhere on the ribbon to activate it. You just have to fill in what you want to extract.
Extract the first name
In the following demo, you have the last name and the first name of your customers in column A.
In an empty column, start to write the text you want to extract.
- First, you write the first first name "Carol"
- Then, for the second cell, just start the first letters
- Automatically, Excel displays a list that follows the pattern of what you have filled in the frst cell 😃😎😍
- If you agree with this list, press Enter
Extract text anywhere in your string
Flash Fill is really a clever tool because it guesses what you want to do.
For instance, in this demo, we extract the Zip Code which is in the middle of the address.
Extract the street name
If your extract is complex with many words, Flash Fill can return the wrong list. But it's not a problem. 😀 Just over-write the text with your correction and Flash Fill will adjust the extract list.
Here we want to extract the street name and city but the first list is not correct.
So we have over-written the text "Prudence Street Detroit" and then, Flash Fill has updated the extract 😃
