FlashFill

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.

Shortcut Ctrl+E

Concatenate

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 the Enter key and all cells are filled in a flash.  8-)

Split

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.


Now, if you want to extract the city, just type Concord into D2. In D3 just type G and hit Enter.

You can see that this does not quite work for San Antonio. :( In fact, right now, you have just extracted the last word of any city. So you need to edit 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, … And all this without a formula.

Numbers

FlashFill can also help you to format numbers.

Let’s say you have a list of phone numbers in column A and you want to these phone numbers to display in the format shown in cell B2.

Once you have typed the number into cell B2, go to cell B3 and enter an opening round bracket. (FlashFill will immediately understand your formatting rules and present all your data with the same format.)

1 comment

  1. Efren

    I got this site from my pal who informed me on the topic of this web page and
    at the moment this time I am visiting this website
    and reading very informative content at this place.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>