Sooner or later a column arrives with too much in it: Byrne, Niamh, DUB-2024-0173, a full address in one cell. The grid creed says one value per cell, so the column has to come apart — and Excel offers two very different tools for the job. One follows rules; one imitates examples. Knowing which to reach for is the actual skill.

Text to Columns: the rule-follower

Data → Text to Columns is the veteran: pick a column, tell it how to cut, and it slices every row identically.

The wizard’s first choice is the one that matters:

  • Delimited — cut at a character: comma, tab, space, or anything you type (a -, a |). Right for exports and jammed-together codes.
  • Fixed width — cut at positions: characters 1–3, 4–9, 10–14. Right for old-system exports where every row is padded to the same width.

Step three of the wizard is the sleeper feature: a type for each new column. This is where you tell Excel that the third slice is a date arriving as DMY — which is exactly how you rescue the dates-as-text plague, and why Text to Columns appears in the cleaning routine even when nothing needs splitting. (Run it with no delimiters at all and it simply re-evaluates a column — the multiply-trick’s respectable cousin.)

One warning, learned the hard way by everyone: the slices land in the columns to the right, overwriting whatever lives there. Insert blank columns first, always.

Flash Fill: the imitator

Flash Fill (Ctrl+E) works the other way round. You don’t describe the rule — you demonstrate the result. In the column beside Niamh BYRNE, type what you want: N. Byrne. Press Ctrl+E, and Excel studies your example, infers the transformation, and fills the column in imitation.

the mess niamh BYRNE SEAN o'brien aoife murphy PADRAIG kelly your example… N. Byrne S. O'Brien A. Murphy P. Kelly Ctrl+E no rule described — Excel infers the pattern from one demonstration
Type one finished example, press Ctrl+E, and the column completes itself by imitation. Magic — which is exactly why it needs checking.

It’s genuinely brilliant for the transformations that are easy to show and tedious to describe: initials from names, the digits out of a postcode, 07701 234567 from a wall of unspaced digits, reassembling Byrne, Niamh as Niamh Byrne.

But hold on to what it is: a guess based on your examples. Flash Fill doesn’t tell you the rule it inferred, and when a row breaks the pattern — a double-barrelled surname, a missing middle part — it improvises silently. So the discipline: give it two or three examples covering the awkward cases, then spot-check the weird rows before trusting the column. And know that the results are values, not formulas — new rows won’t update; you’ll press Ctrl+E again.

Which tool, honestly

Situation Reach for
Clean delimiter, every row identical Text to Columns
Fixed-width system export Text to Columns
Transformation easier to show than describe Flash Fill, then spot-check
Result must update when data changes Neither — TEXTBEFORE / TEXTSPLIT
Same split, every month, forever Power Query

That last pair matters as you move along the path. Text to Columns and Flash Fill are one-off tools — they edit this copy of the data, once. The formula functions give you a split that stays live; Power Query gives you a split that replays itself on every refresh. The rule of thumb from the recipe lesson applies verbatim: doing it twice is fine; the third month, it belongs in the recipe.

One column, one fact — however the data arrived. These two are the quick way there; the path behind them is what makes it permanent.