Back to guide/General Productivity

Data Cleaning Formula

Showing before/after examples lets the AI infer the pattern precisely. Asking for Power Query alternatives when appropriate gives you the best tool for the job, not just formulas.

chatgpt-prompts-for-excelcolumnmessy_formatexample_1
Edit View
Prompt
I have messy data in Excel that needs cleaning:

**Column:** {{column}} (e.g., "Column B, rows 2-1000")
**Current format (messy):** {{messy_format}}
Example messy values:
- "{{example_1}}"
- "{{example_2}}"
- "{{example_3}}"

**Desired clean format:** {{clean_format}}
Expected output for the examples above:
- "{{clean_1}}"
- "{{clean_2}}"
- "{{clean_3}}"

Provide:
1. A formula I can put in an adjacent column to clean each row
2. If the formula is complex, break it into helper columns
3. If Power Query would be better for this scale, show that approach too

My Excel version: {{version}} (e.g., "Excel 365", "Excel 2019")

Variables to customize

{{column}}{{messy_format}}{{example_1}}{{example_2}}{{example_3}}{{clean_format}}{{clean_1}}{{clean_2}}{{clean_3}}{{version}}

Why this prompt works

Showing before/after examples lets the AI infer the pattern precisely. Asking for Power Query alternatives when appropriate gives you the best tool for the job, not just formulas.

Save this prompt to your library

Organize, version, and access your best prompts across ChatGPT, Claude, and Cursor.

Related prompts

Task Decomposition Agent

Forcing the agent to plan before acting prevents premature execution and wasted steps. Explicit dependency mapping enables parallel execution and catches logical gaps early.

Tool Selection Agent

The ReAct pattern (Reason + Act) creates an explicit reasoning trace that improves tool selection accuracy. The error-handling rule prevents infinite retry loops.

Prompt Compressor

Explicitly requiring all functional requirements to be preserved prevents the model from over-compressing and losing critical instructions.

Memory Management Agent

Explicit memory read/write instructions create agents that improve over time. Categorization keeps memories organized, and the deduplication rule prevents context bloat.