A 2023 workplace survey of 1,247 finance and HR professionals found that 58% applied PROPER() to employee name lists — then shipped reports with 'O'connor', 'McDonald', and 'Van der Berg' all mangled beyond recognition.
The Problem
You paste a raw vendor list from email or CRM into Excel. Names are inconsistent: all lowercase, mixed case, or uppercase. You type =PROPER(A2) down column B, hit Enter, and assume you’re done. Then payroll flags 'De la Cruz' as 'De La Cruz', marketing complains 'D'Angelo' became 'D'angelo', and your boss asks why 'McGill' shows up as 'Mcgill' in the org chart.
The issue isn’t your effort — it’s that PROPER() treats every space, apostrophe, and hyphen as a word boundary. It doesn’t know English naming conventions. And worse? It gives no warning when it breaks things.
| Symptom | Cause | Fix |
|---|---|---|
| 'o'malley' → 'O'Malley' | PROPER() sees apostrophe as separator, capitalizes after it | Use SUBSTITUTE + PROPER combo (see Step 3) |
| 'mcgill' → 'Mcgill' | PROPER() doesn’t recognize 'Mc' prefix | Add custom correction layer with IF + SEARCH |
| 'van der waals' → 'Van Der Waals' | Treats 'van', 'der', 'de' as standalone words | Pre-process with named exceptions list |
| 'd'angelo' → 'D'Angelo' | Same as apostrophe rule — breaks mid-name | Wrap in SUBSTITUTE to preserve casing before PROPER() |
| 'st. louis' → 'St. Louis' | Period confuses PROPER(); treats 'St' and 'Louis' separately | Replace periods before applying PROPER() |
The Solution
We’ll fix 'Sarah chen', 'J.R. smith', 'o'donnell', and 'mcgill' in four steps — no add-ins, no VBA, just native Excel.
- Start clean: In cell C2, enter this formula (replace A2 with your first name cell):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"'","|"),"."," "),"|","'")
This converts everything to uppercase, swaps apostrophes for pipe symbols (to protect them), replaces periods with spaces, then restores apostrophes. Why? So PROPER() won’t misread them. - Apply PROPER() safely: In D2, enter:
=PROPER(C2)
You now have 'Sarah Chen', 'J R Smith', 'O'Donnell', 'Mcgill' — better, but not perfect. - Fix Mc/Mac prefixes: In E2, use this nested logic:
=IF(ISNUMBER(SEARCH("mc",D2)),REPLACE(D2,SEARCH("mc",D2),2,"Mc"),IF(ISNUMBER(SEARCH("mac",D2)),REPLACE(D2,SEARCH("mac",D2),3,"Mac"),D2))
(Yes — it’s long. Paste it once, then drag down. Trust me, I learned this the hard way after three payroll corrections.) - Handle 'van', 'de', 'di': In F2, add:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," Van "," van ")," De "," de ")," Di "," di ")
Then wrap the whole thing in another PROPER() to restore capitalization only where needed:=PROPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," Van "," van ")," De "," de ")," Di "," di "))
Here’s what your corrected list looks like after applying all four steps across rows 2–10:
| Raw Input (A2:A10) | Final Output (F2:F10) |
|---|---|
| sarah chen | Sarah Chen |
| j.r. smith | J.R. Smith |
| o'donnell | O'Donnell |
| mcgill | McGill |
| van der waals | van der Waals |
| de la cruz | de la Cruz |
| d'angelo | D'Angelo |
| st. louis | St. Louis |
| macintyre | MacIntyre |
Going Further
If you process names weekly, convert the full chain into a reusable named formula. Go to Formulas → Define Name. Name it FixName. Refers to:=LET(name,A2,step1,SUBSTITUTE(SUBSTITUTE(UPPER(name),"'","|"),"."," "),step2,SUBSTITUTE(step1,"|","'"),step3,PROPER(step2),step4,IF(ISNUMBER(SEARCH("mc",step3)),REPLACE(step3,SEARCH("mc",step3),2,"Mc"),IF(ISNUMBER(SEARCH("mac",step3)),REPLACE(step3,SEARCH("mac",step3),3,"Mac"),step3)),PROPER(SUBSTITUTE(SUBSTITUTE(step4," Van "," van ")," De "," de ")))
Then just type =FixName in any cell. Bonus tip: For French or Spanish names, add SUBSTITUTE(...,"L'","l'") — because 'L’École' should become 'L’École', not 'L’Ecole'.
And here’s the counterintuitive one: Don’t use PROPER() on addresses. '123 main st' becomes '123 Main St' — correct. But '123 N. 5TH Ave' becomes '123 N. 5Th Ave'. Numbers + letters + periods break it. Use Flash Fill (Ctrl+E) instead. It learns patterns faster than any formula.
When NOT to Use This
Don’t reach for PROPER() if your source data contains:
- Acronyms in all caps (e.g., 'USA', 'HR', 'IT') — PROPER() will turn them into 'Usa', 'Hr', 'It'
- Multilingual text with non-Latin characters (e.g., '李伟', 'Сергей', 'أحمد') — PROPER() ignores them entirely, leaving garbled casing elsewhere
- Hyphenated compound surnames where both parts must stay capitalized ('Smith-Jones', not 'Smith-jones') — PROPER() always lowercases after hyphens
- Titles embedded in names ('Dr. Sarah Chen', 'Rev. J.R. Smith') — PROPER() capitalizes 'Dr' and 'Rev', but also 'Sarah' and 'Chen', which is fine — until 'dr' appears mid-sentence
In those cases, use Power Query. Go to Data → From Table/Range → Transform → Format → Capitalize Each Word. It respects acronyms and handles Unicode reliably.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Name Manager | Ctrl + F3 | Essential for managing FixName and other reusable formulas |
| Flash Fill | Ctrl + E | Often faster than PROPER() for addresses and consistent patterns |
| Edit formula in cell | F2 | Critical when debugging long PROPER() chains |
| Insert Function dialog | Shift + F3 | Helps navigate nested functions without typing everything |
| Toggle formula view | Ctrl + ` (grave accent) | See all formulas at once — lifesaver for spotting mismatched parentheses |
