# 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.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Standard
- Tags: Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/standardize-inconsistent-names-and-categories-in-my-data

## Prompt

```text
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.
```

## What to fill in

- Excel / Google Sheets / SQL / Power Query / Python
- CHECK

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.