Split columns and extract repeated text patterns with Text to Columns and Flash Fill in Excel

Quick answer: Use Data → Text to Columns for a known delimiter and Data → Flash Fill for an example-driven pattern. Send results to empty cells and inspect every result; both operations produce a transformed snapshot rather than a formula that automatically follows later source edits.

Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.

Start with source text and empty output columns

In a blank sheet enter Raw in A1, Code in C1 and Colour in D1. Put these strings in A2:A4: A17,Blue, B24,Green, C31,Red. Leave B blank as a visual buffer and C2:D4 empty. Do not use a production worksheet with valuable neighboring data for this first test.

The expected split is three codes A17, B24, C31 and three colours Blue, Green, Red. The comma is the delimiter. It is part of the source text, not a formula operator or an instruction to merge cells.

Split into a chosen destination

1. Select A2:A4, excluding the Raw heading.
2. Choose Data → Text to Columns.
3. Select Delimited, then Next.
4. Select Comma as the delimiter. Clear unrelated delimiters so spaces are not also treated as separators.
5. Inspect the data preview. Each record must form exactly two fields, with the code first and colour second.
6. Continue to the destination step and set Destination to $C$2. Confirm the output area is empty, then select Finish.

Verify C2:D4 against all three source strings and confirm A2:A4 still contain the originals. If a destination warning appears because cells are occupied, cancel and choose a genuinely empty range rather than approving an overwrite reflexively. For codes containing leading zeros, the conversion’s type interpretation needs additional attention; this alphanumeric fixture deliberately avoids an accidental numeric-code conversion.

Use Flash Fill for a rearranged label

Add Label in F1. In F2 type Blue (A17) to demonstrate the desired combination from the split columns. Start F3 with Green (B24) if a second example is needed. Use Data → Flash Fill, or Ctrl+E, in the output column. Inspect the completed values; F2:F4 should read Blue (A17), Green (B24), Red (C31).

A preview is a suggestion, not proof that the transformation is correct. Check punctuation, spaces, full codes and the last row. In more varied data, a short example can teach the wrong pattern; provide another representative example or use a more explicit transformation instead of accepting plausible-looking mistakes.

Change the source to expose the snapshot behavior

Change A3 from B24,Green to B24,Gold. The earlier split and filled values should not be assumed to update. Repeat Text to Columns into a fresh empty pair, H2:I4, with the same delimiter, and check that I3 now says Gold. Create a fresh label example in K2 and repeat Flash Fill for the new split output; K3 should say Gold (B24). Keep the old output temporarily so the difference is easy to see.

Save as XLSX and reopen to inspect the source and both versions. If everything landed in one field, the selected delimiter was wrong; if too many fields appeared, more than one delimiter may have been selected. If Flash Fill does not offer a preview, invoke the documented command manually rather than changing global settings just to finish the exercise.

Official references

• Using Flash Fill in Excel
• Split text into different columns with the Convert Text to Columns Wizard

Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.