Contents
Experiments make us heroes.
(Flash)
Although the instrument Instant Fill (Flash Fill) appeared in Excel since the 2013 version, but for some reason this fact went unnoticed for many users. And absolutely in vain. In many cases, it turns out to be simpler, easier and faster than similar solutions based on formulas or macros. In my experience, in trainings, this topic causes a constant “wow!” audience – regardless of the advancement and / or fatigue of the listeners.
The mechanism of operation of this tool is simple: if you have one or more columns with initial data and you start typing them next to each other in the next column, but in some modified form you need, then sooner or later Excel will hint that it is ready to continue further beyond you:
To reveal the logic (pattern, pattern) of the transformation and run this Excel function, it is usually enough to enter the first 1-3 resulting values manually. If the proposed option suits you, then just click Enter – and the rest of the list will be completed instantly.
If you have already entered the first 2-3 values, and the continuation still does not appear, then you can force the process with a keyboard shortcut Ctrl+E or use button Instant Fill (Flash Fill) tab Data (Date):
Let’s look at a few examples of how this tool can be used in practice to understand its capabilities.
Extracting words from text and permutations
Writing a formula that extracts, for example, the third word from the text in a cell is no small feat. Parse a phrase by space into different columns using Data – Text by Columns (Data — Text to Columns) It’s also not fast. With instant fill, this is done easily and beautifully. Moreover, you can simultaneously change the extracted words in places, combining them in any order:
Dividing the text by register
To highlight words for instant filling, it is absolutely not necessary to have a space. Any other delimiter will work just fine, like a comma or semicolon after importing the CSV file. But what is really cool is that there may not be a separator at all – only capital letters are enough:
It is very difficult to implement such formulas. If without instant filling, then only a macro will help.
Text gluing
If you can divide, then you can glue! Instant Fill will easily assemble a long phrase for you from several fragments, interspersing them with the necessary spaces, commas, unions or words:
Extracting individual characters
Usually, to pull out individual characters and substrings in Excel, functions are used LEVSIMV (LEFT), RIGHT (RIGHT), PSTR (mid) and the like, but instant filling solves this problem with ease. A classic example is the formation of a full name:
Extract only numbers, text or dates
If you have ever tried to pull out only the desired data type from an alphanumeric porridge, then you should understand the complexity of this seemingly simple task. Instant filling and here copes with a bang, but you need a light pendel in the form Ctrl+E:
The same goes for extracting text.
Dates are not a problem either (even if they are written in different formats):
Converting number or date formats
Flash Fill can help change the appearance of existing data or bring it to the same denominator. For example, to convert a regular date “topsy-turvy” to Unix format:
Here the nuance is that before entering, you need to change the format of the resulting cells to text in advance so that Excel does not try to recognize the “wrong” dates entered manually as a sample.
Similarly, you can also correctly represent phone numbers by adding the country code and the three-digit operator (city) prefix in brackets:
Do not forget to first change the format of the cells in column B to text – otherwise Excel will treat values uXNUMXbuXNUMXbbeginning with a “+” sign as formulas.
Convert text (numbers) to date
When downloading from various ERP and CRM systems, the date is often represented as an 8-digit number in the YYYYMMDD format. You can convert it to a normal form either by the function DATA IDENTIFIER (DATEVALUE), or much easier – instant filling:
Change case
If you got a text with the wrong case, then you can simply hint in the next column what kind you want to convert it to – and instant filling will do all the work for you:
It will be a little more difficult if you need to change the case differently for different parts of the text. For example, capitalize only the second word, leaving the first in its normal form. Here, two values entered as a sample will not be enough and you will have to make changes that instant filling will immediately take into account in the results:
Limitations and nuances
There are a few things to keep in mind when using Flash Fill in your work:
- It only works if enter samples strictly side by side – in the previous or next column to the right of the data. If you retreat one empty column from the original text, then nothing will work.
- When a pattern is found all values in a row are taken into account — to the left and to the right of the input column. Moral: extra columns that can confuse the algorithm or introduce noise should be separated from the working data in advance by empty columns or deleted.
- Instant Fill works great in smart tables.
- slightest error or typo when typing sample cells can cause flash fill to fail to reveal the pattern and not work. Be careful.
- There are situations where the template is defined incorrectly, so always need to check findingsthat you received (at least selectively).
- How to extract the last word from the text in a cell
- Fuzzy Text Search (Pushkin = Pushkin) with Fuzzy Lookup in Excel
- Three Ways to Paste Text in Excel