Flash Fill and Text to Columns: Split and Join Text Without Formulas
Zoya gets a list of student emails like amir.pune@example.com and needs two clean columns: Name and City. She types Amir next to the first email, presses Ctrl + E, and Excel fills the rest. For branch codes like PUN-0142-EXL she uses Text to Columns to split at every dash.
Flash Fill (Ctrl + E) copies a pattern you type by example. Text to Columns (Data tab) splits one column into several at a delimiter, like a comma or dash, or at fixed widths. Both give fixed values, not formulas, so they do not update when the source changes.
Download the practice file (flash-fill-text-to-columns.xlsx)
Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Type the result you want for the first row, for example
Amirnext to amir.pune@example.com. - Press Enter, then Ctrl + E in the next cell (or Data > Flash Fill).
- Check a few rows. If one is wrong, fix it and Excel corrects similar rows.
- To split by a dash, select the code column and click Data > Text to Columns > Delimited > Next.
- Tick Other and type
-, choose a Destination cell, then Finish.
Flash Fill by example
| Type this once | Flash Fill gives | |
|---|---|---|
| amir.pune@example.com | Amir | Zoya, Raja, Rani, Salman ... |
| amir.pune@example.com | Pune | Nashik, Kolhapur, Nagpur ... |
| (Name and City) | Amir (Pune) | Zoya (Nashik), Raja (Kolhapur) ... |
- Click C2 and type
Amir. Press Enter. - In C3 press Ctrl + E. Excel fills C3:C8 with the names.
- Click D2, type
Pune, Enter, then Ctrl + E in D3. - Excel learns capital letters too: you typed Amir, not amir, so every name starts with a capital.
Flash Fill does not update
If an email changes later, the name next to it does not. For lists that change, use formulas (next section).
The same split with formulas (updates by itself)
=PROPER(LEFT(A2,FIND(".",A2)-1))
=PROPER(MID(A2,FIND(".",A2)+1,FIND("@",A2)-FIND(".",A2)-1))
The first formula takes the text before the dot and capitalises it: Amir. The second takes the text between the dot and the @: Pune. In Microsoft 365, =PROPER(TEXTBEFORE(A2,".")) and =PROPER(TEXTBEFORE(TEXTAFTER(A2,"."),"@")) do the same.
Text to Columns for codes
- Select B2:B8 (codes like PUN-0142-EXL).
- Click Data > Text to Columns.
- Choose Delimited, click Next.
- Tick Other and type
-. The preview shows three columns. - Click Next. Select the middle column and set Text so 0142 keeps its leading zero.
- Set Destination to
$G$2so the original column is not overwritten, then Finish.
Result for row 2: PUN, 0142, EXL. The practice file checks the first two parts with =LEFT(B2,3) and =MID(B2,5,4).
Which tool to use
| Situation | Use |
|---|---|
| One-time cleanup of a pasted list | Flash Fill |
| Split at a clear delimiter (comma, dash, space) | Text to Columns |
| List that is refreshed every week | Formulas or Power Query |
| Fixed-width text from an old system | Text to Columns > Fixed width |
Ravindra Bagale's Tip
After Flash Fill, sort the new column A to Z and look at the first and last few rows. Odd results like a blank or a half name collect there, so you spot mistakes in seconds.
Ravindra Bagale's Tip – मराठी
Flash Fill नंतर नवीन column A to Z sort करा आणि सुरुवातीच्या आणि शेवटच्या काही rows बघा. रिकामं किंवा अर्धवट नाव असे विचित्र results तिथेच जमतात, त्यामुळे चुका काही सेकंदात दिसतात.
Ravindra Bagale's Tip – हिंदी
Flash Fill के बाद नया column A to Z sort कीजिए और शुरू और आख़िर की कुछ rows देखिए। खाली या आधा नाम जैसे अजीब results वहीं इकट्ठा होते हैं, तो गलतियाँ सेकंडों में दिख जाती हैं।
Practice task
- Open the Emails sheet. Type
Amirin C2 and press Ctrl + E in C3. - Type
Punein D2 and Flash Fill the cities. - Compare columns C and D with the formula columns E and F.
- Select B2:B8 and split the codes with Data > Text to Columns at the dash, destination G2 (overwrite the check formulas if Excel asks).
- Type
Amir (Pune)in a new column and Flash Fill the rest.
Learn it properly
Course lessons:
Related guides: Excel Text Functions for Data Cleanup: TRIM, PROPER, LEFT, MID, FIND · CONCAT and TEXTJOIN · Remove Duplicates
Frequently asked questions
What is the shortcut for Flash Fill?
Ctrl + E. You can also use Data > Flash Fill or Home > Fill > Flash Fill.
Why does Flash Fill not work?
Type at least one example right next to the data, in the same table, and make sure File > Options > Advanced > Automatically Flash Fill is on. Some patterns need two examples.
How do I keep leading zeros in Text to Columns?
On the last step, select that column and choose Column data format: Text.
Can Text to Columns split into rows?
No, only into columns. Use Power Query Split Column > By Delimiter > Rows for that.