Splitting Text
If we were to receive a list of names where the first and last names are both stored in a single cell, we can split the names by selecting the cell to the right of the first name in the list and typing the first name. After you press Enter, press CTRL-E to generate a list of the remaining first names.
We can now select the first cell to the right of the newly created first names list and type the last name. After you press Enter, press CTRL-E to generate a list of the remaining last names.
PRO TIP: Think of it this way: type a version of the data the way you WISH it were to appear, then press CTRL-E.
Concatenating Text
To concatenate text, such as first and last names into a full name, type the first full name then press CTRL‑E.
Email Addresses
If you have a list of names and you need to generate email addresses for the names, type the first email address then CTRL‑E. NOTE: This process will NOT consider duplicate email address as problematic; these will need to be adjusted manually.
Changing Case
If you received a list of names in all lower case letters and you wish to have the first character of each name capitalized, type the first name in the proper case format and press CTRL‑E.
Initials
If you received a list of names and you wish to generate a list of initials, type the first user’s initials and press CTRL‑E.
Extracting Years, Months, and Days
To extract the year from a date, type the year of the first date and press CTRL‑E.
PRO TIP: If you are extracting months and days, Flash Fill can become confused when encountering a day and month that have the same number (i.e. 9/9). In cases like this, select a different row in the data that does not have the same day/month value and press CTRL‑E. The list will Flash Fill in both directions. Any case where the first entry in the list confuses Flash Fill, create your example on a different row that may not be as confusing to Flash Fill’s programming. This may take some trial and error.
Important Points about Flash Fill Operation
- Flash Fill is NOT dynamic. Once the list is generated, there is no connection back to the original data. Any changes in the original data will not carry forward to the successive lists.
If this behavior is required, you will need to invest some time to create more dynamic formulas using some of the functions mentioned in the list at the beginning of this lecture.
- The new list generated by Flash Fill must be on the same rows and directly to the left or right of the source column.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
