Prompt Almanac

Create beginner Works with: Any AI

Excel Text to Columns and Flash Fill Cleaning Guide

Structured brief Has example

Choose the right quick technique for splitting and reshaping text.

Open in ChatGPTOpen in Claude

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Rate this prompt

Loading ratings…

I need to clean a column of text in Excel. Examples (ten rows exactly as they appear): [EXAMPLES]. What each result should look like: [DESIRED_RESULT]. Whether it is a one-off or will be refreshed: [ONE_OFF_OR_REPEAT].

Recommend the best approach among Text to Columns, Flash Fill, formulas and Power Query, and explain the differences: which updates automatically, which is quick but static, and which handles odd cases. Give the steps for the one you recommend, with the first three rows worked out before and after, and list the cases in my examples that might break the method. Explain why Flash Fill may guess wrongly from too few examples, and how to check: sort the results, filter for blanks, compare lengths.

Include checks (row count, blank results, a sample compared by eye). Work on a copy and keep the original column. Show how to convert final values to plain text safely and remove helper columns. Mark menu names [VERIFY].

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

See an example result (fictional data)
Invented column of ten names such as 'Smith, Jane', 'Patel, Raj', 'O'Brien, Aoife'. Goal: separate first name and surname.
Option 1, Text to Columns with comma as separator, then trim: fast, static. Option 2, Flash Fill: type 'Jane' next to the first row and 'Raj' next to the second; Excel suggests the rest; check unusual names such as 'O'Brien, Aoife' and any with two commas. Option 3, formulas: use for data that will be refreshed. Result for row 3: surname O'Brien, first name Aoife. Check: count of non-blank first names equals the number of rows (10).

Illustrative only, made-up data. Your results will differ and must be reviewed.

Related prompts