Standardize inconsistent names and categories in my data
Builds a mapping table that cleans up spelling, case, abbreviation, and naming variants, plus a naming convention going forward.
The prompt
Help me clean up inconsistent naming in my data so the same thing is always written the same way.
Data: [PASTE THE COLUMN VALUES, or a list of distinct values with counts]
Columns affected: [e.g. customer names, product categories, country, department, job titles]
Preferred standard, if any: [e.g. official customer list, ISO country codes, our product catalog]
Tool: [Excel / Google Sheets / SQL / Power Query / Python]
Look for:
- Spelling and typo variants ("Recieved" vs. "Received").
- Case, spacing, and punctuation differences ("ACME Inc.", "Acme Inc", "acme inc ").
- Abbreviations and synonyms ("NY", "New York", "NYC").
- Old and new names for the same thing after a rebrand or reorganization.
- Values in the wrong column, or two values combined in one cell ("Sales - East").
Return:
1. A mapping table: Original value | Count (if given) | Standard value | Reason | Confidence (high / check).
2. Values you couldn't match confidently, for me to decide.
3. A naming convention for each column going forward: format rules and allowed values.
4. How to apply the mapping in my tool (e.g. a lookup table with XLOOKUP, a Power Query merge, or a CASE statement).
5. A way to stop it recurring, such as a dropdown list or data validation rule.
Only group values when the evidence is strong, and mark uncertain matches [CHECK] rather than merging them. Two similar names can be two different customers or products.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- Excel / Google Sheets / SQL / Power Query / Python
- CHECK
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Design an A/B testHypothesis, variants, metrics, sample size, and duration for a valid test.
- Break down my results into the business drivers behind themBuilds a driver tree from your results, sizes how much each driver contributed to the change, and names which ones to act on.
- Identify the leading and lagging indicators for my business or teamSorts your metrics into leading and lagging indicators, links each lead to the outcome it predicts, and fills gaps in early-warning measures.
- Find the 20% of activities driving 80% of my resultsRuns a Pareto analysis to show the few activities, customers, or products producing most of your results, and what to cut or double down on.