Extract text without a formula

In Excel 2013 and later, you can extract text without a formula using Flash Fill.

What is Flash Fill?

FlashFill 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 First Name without formula

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 text anywhere in your string with FlashFill

Extract from a complex string

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 😃

FlashFill Complex string

Related posts


Permanent link to this article: https://www.excel-exercise.com/extract-text-without-formula/


Leave a Reply

Your email address will not be published.