Clean and Standardize Name Columns in Excel Automatically

Tested prompts for standardize names in excel column compared across 5 leading AI models.

BEST BY JUDGE SCORE Claude Opus 4.7 9/10

You have a column of names in Excel that looks like a mess. Some are ALL CAPS, some are lowercase, some have extra spaces, some include titles like 'Dr.' or suffixes like 'Jr.', and a handful are formatted 'Last, First' while the rest are 'First Last'. You need them consistent before you can dedupe, mail merge, import into a CRM, or run a VLOOKUP that actually matches.

Excel's built-in tools like PROPER(), TRIM(), and Flash Fill handle the easy cases but break on edge cases: 'mcdonald' becomes 'Mcdonald' instead of 'McDonald', 'o'brien' loses its capitalization, and nicknames in parentheses confuse everything. That's where an AI prompt does the heavy lifting. You paste the column, describe the format you want, and get back a cleaned version that respects real-world name patterns.

This page gives you the exact prompt, four model outputs you can compare, and the scenarios where this approach beats a formula. Use it to decide whether to run your list through ChatGPT, Claude, Gemini, or stick with Excel-native tools.

When to use this

Reach for an AI prompt when your name column has inconsistent casing, mixed formats, or edge cases that formulas keep botching. This works best on lists under a few thousand rows where accuracy matters more than speed, and when you need judgment calls on things like prefixes, suffixes, and compound surnames.

Cleaning a contact list before importing to Salesforce, HubSpot, or Mailchimp
Preparing attendee lists for name badges or certificates where 'MARY SMITH' needs to become 'Mary Smith'
Merging two spreadsheets where one uses 'Last, First' and the other uses 'First Last'
Normalizing donor or alumni records with titles like Dr., Prof., Rev. that need to be separated or preserved consistently
Fixing international names with accents, hyphens, or particles like 'van der' that PROPER() mangles

When this format breaks down

Lists over 10,000 rows where copy-paste into a chat window becomes impractical. Use Power Query or a Python script instead.
Data with strict privacy requirements (patient records, minors, financial PII) that cannot be pasted into a third-party AI service.
When you only need basic case correction on clean data. =PROPER(TRIM(A2)) runs instantly with no setup.
Highly ambiguous names where you cannot verify the output, like transliterated names from non-Latin scripts where the AI may guess wrong.

The prompt we tested

You are an expert Excel data-cleaning assistant. Your task is to standardize the names in the user's provided column so they follow a consistent, professional format (proper capitalization, consistent name order, trimmed whitespace, removed titles/suffixes as appropriate, and corrected punctuation).

Instructions:
Output proper case names (e.g., 'John Smith'), trim extra spaces, remove titles like Mr./Dr. and suffixes like Jr. unless the user says otherwise, and keep the name order as First Last. Keep the response concise, use a clean markdown table for the before/after, and make sure the Excel formula is copy-paste ready for cell B2 referencing A2.

Here is the user's name column data and any context:
Here is column A from my contacts spreadsheet that I need cleaned up: ' john SMITH', 'Mary-jane O'CONNOR', 'Dr. robert Downey jr.', 'LISA   nguyen', 'pedro  GARCIA-lopez'. I want them all in a consistent First Last format for a mail merge.

Return your response in this structure:
1. A cleaned two-column table (Original | Standardized) showing each name before and after.
2. A short bullet list of the standardization rules you applied.
3. A ready-to-paste Excel formula (or Power Query / TEXTSPLIT approach) the user can apply to the whole column to reproduce the cleanup themselves.

Model comparison

Model	Quality	Cost	Latency	Tokens
Claude Opus 4.7	9	$0.0831	13.5s	480 / 1012
Claude Haiku 4.5	8	$0.0026	5.1s	324 / 457
GPT-5.4	Not run yet
Gemini 2.5 Pro	Not run yet
Grok 4.1 Fast Reasoning	9	$0.0002	11.8s	439 / 233

Outputs

Claude Opus 4.7 9/10

# Standardized Names

## 1. Before / After Table

| Original | Standardized |
|---|---|
| ` john SMITH` | John Smith |
| `Mary-jane O'CONNOR` | Mary-Jane O'Connor |
| `Dr. robert Downey jr.` | Robert Downey |
| `LISA   nguyen` | Lisa Nguyen |
| `pedro  GARCIA-lopez` | Pedro Garcia-Lopez |

## 2. Rules Applied

- **Trimmed** leading, trailing, and duplicate internal spaces.
- **Proper Case** applied to every name part (first letter uppercase, rest lowercase).
- **Preserved capitalization after punctuation** — hyphens (`Mary-Jane`, `Garcia-Lopez`) and apostrophes (`O'Connor`).
- **Removed titles** (e.g., `Dr.`, `Mr.`, `Mrs.`, `Ms.`, `Prof.`).
- **Removed suffixes** (e.g., `Jr.`, `Sr.`, `II`, `III`).
- **Kept order** as *First Last*.

## 3. Copy-Paste Excel Formula (cell **B2**, referencing **A2**)

Works in Excel 365 / Excel 2021 (uses `LET`, `TEXTSPLIT`, `MAP`, `TEXTJOIN`):

```excel
=LET(
  raw, TRIM(A2),
  noTitle, TRIM(IFERROR(REDUCE(raw,{"Dr.","Mr.","Mrs.","Ms.","Miss","Prof.","Rev.","Sir"},LAMBDA(a,t,SUBSTITUTE(a,t,"",1))),raw)),
  noSuffix, TRIM(IFERROR(REDUCE(noTitle,{" Jr."," Sr."," II"," III"," IV"," Jr"," Sr"},LAMBDA(a,s,SUBSTITUTE(a,s,""))),noTitle)),
  parts, TEXTSPLIT(noSuffix," ",,TRUE),
  fixed, MAP(parts, LAMBDA(p,
     TEXTJOIN("-",TRUE, MAP(TEXTSPLIT(p,"-"), LAMBDA(h,
        TEXTJOIN("'",TRUE, MAP(TEXTSPLIT(h,"'"), LAMBDA(x,
           UPPER(LEFT(x,1))&LOWER(MID(x,2,LEN(x)))
     )))
  ))))),
  TEXTJOIN(" ",TRUE,fixed)
)
```

**How to use:** Paste into **B2**, then drag the fill handle down column B to apply to every row in column A. The formula handles mixed casing, extra spaces, hyphenated surnames, apostrophes (O'Connor), titles, and suffixes in one pass.

Claude Haiku 4.5 8/10

# Name Standardization Results

## Before & After Table

| Original | Standardized |
|----------|-------------|
| ' john SMITH' | John Smith |
| 'Mary-jane O'CONNOR' | Mary-jane O'connor |
| 'Dr. robert Downey jr.' | Robert Downey |
| 'LISA   nguyen' | Lisa Nguyen |
| 'pedro  GARCIA-lopez' | Pedro Garcia-lopez |

## Standardization Rules Applied

- ✓ Trimmed leading/trailing whitespace and extra spaces between words
- ✓ Applied proper case (First letter capitalized, rest lowercase)
- ✓ Removed titles (Dr., Mr., etc.)
- ✓ Removed suffixes (Jr., Sr., etc.)
- ✓ Preserved hyphenated surnames and middle names (Mary-jane, Garcia-lopez, O'connor)
- ✓ Maintained First Last name order

## Excel Formula

Copy this formula into cell **B2** and fill down:

```excel
=PROPER(TRIM(REGEX(REGEX(A2,"^\s*(Dr\.|Mr\.|Mrs\.|Ms\.|Prof\.|Sr\.|Jr\.|III|II|IV)\s+",""),"(Jr\.|Sr\.|III|II|IV)\.?\s*$","")))
```

**Alternative (if REGEX unavailable):**
```excel
=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Dr. ",""),"Jr.",""),"Sr.",""),"Mr. ",""),"Mrs. ","")))
```

**For most robust results, use Power Query:**
- Load column A → Add Custom Column → 
```
Text.Proper(Text.Trim(Text.Clean(A)))
```
- Then manually remove titles/suffixes in a second step, or add pattern matching as needed.

Grok 4.1 Fast Reasoning 9/10

1. | Original              | Standardized       |
   |-----------------------|--------------------|
   | ' john SMITH'         | John Smith         |
   | 'Mary-jane O'CONNOR'  | Mary-Jane O'Connor |
   | 'Dr. robert Downey jr.' | Robert Downey    |
   | 'LISA   nguyen'       | Lisa Nguyen        |
   | 'pedro  GARCIA-lopez' | Pedro Garcia-Lopez |

2. - Trimmed leading/trailing/extra whitespace.
   - Applied proper case (Title Case) to all parts.
   - Removed titles (e.g., Dr.) and suffixes (e.g., jr.).
   - Preserved hyphens/apostrophes; maintained First Last order.

3. Paste this into B2 and drag down:  
   `=TRIM(PROPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"Dr. ",""),"Mr. ",""),"Mrs. ",""),"Ms. ","")," Jr.","")))`

   *(Handles common titles/suffixes; extend SUBSTITYUTE for others like "Sr." if needed.)*

What makes these work

01

Show the AI your exact target format
Include two or three before/after examples in your prompt. 'Convert SMITH, JOHN to John Smith' is clearer than 'standardize these names'. The model matches your pattern instead of guessing.
02

Process in batches of 50 to 200 rows
Large pastes get truncated or lose accuracy mid-list. Break a 2,000-row column into chunks, run each through the prompt, and stack results back into Excel. Spot-check every batch.
03

Keep the original column
Paste AI output into a new column beside the original. This lets you run a quick IF() comparison to flag rows where the AI changed something unexpected, and gives you a rollback if a batch goes sideways.
04

Specify how to handle edge cases
Tell the prompt explicitly what to do with suffixes (Jr., III), titles (Dr., Prof.), and single-name entries. Ambiguous instructions produce inconsistent output across the column.

More example scenarios

#01 · Mixed case and spacing in a sales CRM export

Input

  JOHN smith
mary JONES  
Bob   o'brien
SARAH mcdonald
 jennifer van der berg

Expected output

John Smith
Mary Jones
Bob O'Brien
Sarah McDonald
Jennifer van der Berg

#02 · Converting 'Last, First' to 'First Last' for a mail merge

Input

Smith, John
Jones, Mary-Anne
O'Brien, Robert Jr.
Van Buren, Martin
Patel, Priya K.

Expected output

John Smith
Mary-Anne Jones
Robert O'Brien Jr.
Martin Van Buren
Priya K. Patel

#03 · Stripping titles from a medical conference registration list

Input

Dr. Emily Chen, MD
Prof. Ahmed Hassan, PhD
Ms. Linda Park
Mr. James O'Connor III
Dr. Dr. Robert Lee

Expected output

Emily Chen
Ahmed Hassan
Linda Park
James O'Connor
Robert Lee

#04 · Separating nicknames from university alumni records

Input

Michael (Mike) Johnson
Elizabeth "Liz" Warren
Robert Bob Smith Jr.
Christopher Chris O'Neill
Katherine (Kate) McAllister

Expected output

Michael Johnson | Mike
Elizabeth Warren | Liz
Robert Smith Jr. | Bob
Christopher O'Neill | Chris
Katherine McAllister | Kate

#05 · Normalizing accented international names for a global HR system

Input

josé garcía
FRANÇOIS DUBOIS
müller, hans
O'SULLIVAN, SIOBHÁN
de la cruz, maría josé

Expected output

José García
François Dubois
Hans Müller
Siobhán O'Sullivan
María José de la Cruz

Common mistakes to avoid

Trusting PROPER() on Irish and Scottish names
Excel's PROPER function turns 'MCDONALD' into 'Mcdonald' and 'o'brien' into 'O'Brien' only by accident. Compound capitalization like McDonald, MacLeod, and DeShawn needs either an AI pass or a custom lookup.
Forgetting to TRIM first
Leading and trailing spaces invisible to the eye will break exact-match joins later. Run TRIM or ask the AI to strip whitespace explicitly, otherwise 'John Smith ' and 'John Smith' read as different values.
Losing data by overwriting the source column
Pasting AI output directly over the original deletes your audit trail. If the model mis-corrects a name you will have no way to recover it without re-exporting from the source system.
Ignoring locale-specific conventions
Spanish names often carry two surnames, Dutch names use lowercase particles like 'van' and 'de', and Hungarian lists surnames first. A one-size prompt flattens these into Anglo-American format and introduces errors.
Skipping the spot-check on large lists
AI models occasionally hallucinate name changes, especially on rare or non-Latin names. Always scan the output column and compare counts. A 500-row input should produce exactly 500 output rows.

Related queries

Frequently asked questions

How do I standardize names in Excel without using AI?

Use =PROPER(TRIM(A2)) for basic case and space cleanup. For 'Last, First' to 'First Last' conversion, combine MID, FIND, and LEN, or use Text to Columns with a comma delimiter. Flash Fill (Ctrl+E) handles simple patterns if you type two or three examples.

Why does PROPER() capitalize McDonald wrong?

PROPER only capitalizes the first letter of each word and lowercases the rest, so McDonald becomes Mcdonald. Excel has no awareness of Scottish, Irish, or compound naming conventions. You need either a custom VBA function, a lookup table of known exceptions, or an AI prompt.

How do I split full names into first and last name columns?

Use Text to Columns with a space delimiter for simple two-word names. For names with middle initials, suffixes, or compound surnames, use =LEFT(A2,FIND(' ',A2)-1) for first name and =MID(A2,FIND(' ',A2)+1,100) for the rest, then clean manually or with AI.

Can Flash Fill standardize a whole name column?

Flash Fill works for simple, consistent patterns. Type the cleaned version of the first two or three names, then press Ctrl+E. It fails on inconsistent source data, mixed formats, or when edge cases differ from your examples.

How do I remove titles like Dr. and Mr. from a name column?

Use SUBSTITUTE nested for each title: =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"Dr. ",""),"Mr. ","")). For many titles, an AI prompt is faster. Make sure to include the trailing space in the substitute argument so you do not leave double spaces.

What's the best way to dedupe a name list after standardizing?

After standardization, use Data > Remove Duplicates on the cleaned column. For fuzzy matches where 'John Smith' and 'Jon Smith' should be treated as the same, use Power Query's fuzzy matching or an AI prompt that flags likely duplicates with a similarity score.