Flash Fill lets Excel split or join text for you after you type one or two examples. Common jobs are splitting full names, combining first and last names, and building email addresses, all without a formula. It has one catch: the results never update, so this guide also covers what to use when your data changes.
Combine first and last names with Flash Fill
Microsoft's support page uses this layout: first names in column A, last names in column B, and the combined name in column C.
- In cell C2, type the full name exactly as you want it to look, then press Enter.
- Start typing the next full name in C3. Excel senses the pattern and shows a preview of the rest of the column filled in.
- Press Enter to accept the preview.
Splitting works the same way in reverse. Microsoft's page lists separating first and last names from one column as one of Flash Fill's jobs: put the full names in one column, type the first name from row 2 in the next column, and let Excel pick up the pattern. Do the same in another column for last names.
Flash Fill can do more than names. Excel Easy's tutorial shows it extracting numbers, combining last and first names into email addresses, reformatting numbers into a social security format, and rearranging digits.
Run Flash Fill manually with Ctrl+E
If no preview appears, you can force it. Click in the column where you typed your example, then either:
- Press Ctrl+E, or
- Go to Data > Flash Fill. Excel Easy places the button in the Data Tools group on the Data tab.
This is also the faster habit: type one example, press Ctrl+E, done.
Turn on Automatically Flash Fill if previews never show
No preview usually means automatic Flash Fill is off, according to Microsoft. The menu path depends on your platform:
- Mac: Microsoft's page says go to Tools > Options > Advanced > Editing Options and check Automatically Flash Fill. That is a Mac-style menu.
- Windows: Excel's Options live under the File tab instead. Microsoft's page doesn't spell out the Windows path.
Ctrl+E works even when the automatic option is off, so you don't strictly need to change the setting.
Fix wrong Flash Fill results by correcting one cell
Flash Fill only works when it recognizes a pattern, and sometimes it guesses wrong. In Excel Easy's decimal-number example, Flash Fill pulled out only the digits after the decimal point. The fix was to correct one wrong result right after running it (changing B3 to 26.2). Excel then extracted the rest correctly.
So if a fill looks off, don't start over. Overtype the first bad cell with the right value and let Excel re-read the pattern. Then scan the column before you rely on it, especially with names that have middle names or prefixes.
Why Flash Fill doesn't update when your data changes
This is the limit Microsoft's page leaves out. Flash Fill output is static text, not a formula. Excel Easy shows that changing A1 to 200 leaves B1 unchanged. Office Watch (13 July 2026) puts it plainly: Flash Fill runs once on the rows in front of it, and editing a cell, adding a row or refreshing data does nothing.
That's fine for a one-off cleanup. If the list will grow or change, use one of the options below instead.
Live formulas: TEXTBEFORE and TEXTAFTER
For names with one first name and no middle name, Office Watch suggests these. With the full name in A2:
=TEXTBEFORE(A2," ")
=TEXTAFTER(A2," ")
The first returns the first name, the second the last name. Office Watch's printed version uses a table reference ([Name]) instead of A2, and its first formula has a typo with a curly bracket, so use square brackets if you copy it into a table. These functions, along with TEXTSPLIT, update when the source changes, but Office Watch says they handle exceptions poorly.
To go the other way and join names with a formula, see Microsoft's linked article, "Combine text from two or more cells into one cell", from the Flash Fill page. If you work in Sheets too, the same idea is covered in How to Combine Cells in Google Sheets With TEXTJOIN.
Regex for surnames with prefixes
Office Watch says REGEXTEST, REGEXEXTRACT and REGEXREPLACE are in Excel 365 for Windows and Mac. This formula splits a name into two cells:
=REGEXEXTRACT(A2,"^(\S+)\s+(.*)$",2)
The final 2 (return_mode) returns the capture groups as an array that spills across two cells. The pattern treats the first run of non-space characters as the first name and everything after the space as the surname, which keeps surnames with "van" and "Al" prefixes together.
Power Query's Column from Examples
This is the closest thing to a refreshable Flash Fill:
- Select your data and go to Data > Get & Transform > From Table/Range.
- In the Power Query Editor, go to Add Column > Column from Examples and type an example.
- Click Close & Load.
When the source changes, Refresh reruns the steps. Office Watch says Power Query is in Excel 365, Excel 2024 and earlier for Windows, and in Excel 365 for Mac, but Column from Examples is not available on the Mac.
Copilot
Office Watch also notes that Copilot's side pane or the COPILOT() function can split names with exceptions, and advises sending many cells in one formula to save AI credits.
Which Excel versions have Flash Fill
Microsoft's page lists Excel for Microsoft 365, Excel 2024 and Excel 2021 (Windows and Mac), plus Excel 2019 and 2016. Excel for the web isn't on that list. Microsoft doesn't describe any plan or licence limit for Flash Fill itself.
Once your names are split, if the list has repeats, How to Remove Duplicates in Excel Without Losing Data is the next step.
