2.2 Flash Fill
Flash Fill watches one or two examples you type and fills the rest of the column with the same pattern. Available in Excel 2013 and later.
Steps in Excel
- Put the source data in column A (e.g.
ravina.patil@example.com– see example below). - In B2 type the result you want for row 2 (e.g.
ravina). - Go to B3 and press Ctrl + E (or Data › Data Tools › Flash Fill, or Home › Editing › Fill › Flash Fill).
- Check the results. If a few are wrong, correct one of them and Flash Fill again.
Worked example. Store codes in our export are combined like PUN-Kothrud-01. Shraddha wants City code and Area separately.
| Store Code (A) | City Code (B – typed first row, Ctrl+E) | Area (C – typed first row, Ctrl+E) |
|---|---|---|
| PUN-Kothrud-01 | PUN | Kothrud |
| NSK-College Road-01 | NSK | College Road |
| NGP-Sitabuldi-02 | NGP | Sitabuldi |
| KOP-Tarabai Park-01 | KOP | Tarabai Park |
Flash Fill also combines: type Kothrud, Pune in a new column from Area and City, press Ctrl + E.
Ravindra Bagale's Tip
Lakshat theva, Flash Fill cha result formula nasto – to fakt values asto. Source data badalla tar Flash Fill column update hot nahi. Roj badalnarya data sathi formula (TEXTBEFORE, LEFT, MID – Module 3) kiwa Power Query vapra; Flash Fill one-time cleaning sathi best aahe.
Practice task
From a column of customer e-mails like shahrukh@example.com, use Flash Fill to extract the name part with the first letter capital (Shahrukh). Then combine Area and City into "Area, City".