In the pack: ChatGPT, Claude & Gemini Prompts for Data Analysis, Excel & Google Sheets
Split One Messy Column Into Clean Fields
Prompt
The column and what it holds (for example full address, "Surname, First", product code with size and colour): {{COLUMN_DESCRIPTION}}
Eight sample values, including awkward ones, and the fields I want out: {{SAMPLES_AND_TARGET_FIELDS}}
Tool: {{TOOL}}
Do this:
1. Describe the pattern or patterns you can see, and list the sample values that break the main pattern.
2. Choose a method (Text to Columns, a formula, Flash Fill, Power Query split, or a short script) and justify it. Prefer repeatable methods over one-off ones, and say why Flash Fill is not repeatable.
3. Give exact steps or formulas, including how to handle missing parts and extra separators.
4. Show a table of my samples with the output fields as your method would produce them, marking any that come out wrong.
5. Suggest a validation: a count of rows where the number of parts is not what I expect.
Do not guess how to split ambiguous values (for example a three-word surname); put them on a "needs review" list. Remind me to work on a copy of the column.
Customize this prompt
Fill in the variables below to personalize this prompt:
Model Settings
Recommended Models
Temperature
Max Tokens
Additional Notes
About this prompt
“Split One Messy Column Into Clean Fields” is a free prompt for ChatGPT in the Productivity category, 187 words long, added to The Prompt Index on October 28, 2026 by The Prompt Index. Copy it with the button above, paste it into ChatGPT or any other AI chat assistant, and send it.
It has 3 placeholders to fill in first:
{{COLUMN_DESCRIPTION}}, {{SAMPLES_AND_TARGET_FIELDS}}, {{TOOL}}.
Use the “Customize this prompt” fields to personalise it before you copy.
Recommended models: ChatGPT, Claude, Gemini.
0 upvotes, sent to an AI tool 0 times, viewed 3 times.
Browse all Productivity prompts, the AI prompt database or use the AI prompt finder to search every prompt.