PROPER: Title Case Conversion (2025)
Quick Answer: PROPER converts to title case: =PROPER('john smith') returns 'John Smith'. Capitalizes first letter of each word, lowercase for rest. Use for names, titles, addresses. Has quirks with apostrophes and special names like McDonald.
What is PROPER?
PROPER converts text to title case by capitalizing the first letter of each word and making all other letters lowercase. Perfect for formatting names, addresses, and titles.
Before & After:
Input
john SMITH
NEW york
MAIN STREET
=PROPER(A1)
Output
John Smith
New York
Main Street
How PROPER Works:
1. Identifies Word Boundaries
Spaces, apostrophes, numbers act as word separators
2. Capitalizes First Letter
First letter of each word becomes uppercase
3. Lowercase Everything Else
All other letters converted to lowercase
Common Use Cases:
✓ Names
john smith → John Smith
✓ Addresses
123 main street → 123 Main Street
✓ Cities/States
new york → New York
✓ Titles
marketing manager → Marketing Manager
⚠️ PROPER Quirks to Know:
Apostrophes Trigger Capitalization
o'brien → O'Brien ✓
can't → Can'T ✗ (wrong capitalization)
Doesn't Handle Special Names
mcdonald → Mcdonald ✗ (not McDonald)
deangelo → Deangelo ✗ (not DeAngelo)
Numbers Start New Words
address1main → Address1Main
No Exceptions for Articles
lord of the rings → Lord Of The Rings
(Capitalizes 'Of' and 'The')
💡 When to Use PROPER
- • Bulk name cleanup: Fix ALL CAPS or all lowercase imported data
- • Address formatting: Standardize street addresses and cities
- • Professional documents: Make spreadsheets look polished
- • Good enough is fine: Accept quirks for 95% accuracy on large datasets
- • NOT for special cases: Manual fixes needed for McDonald, O'Neill, etc.
PROPER Syntax
=PROPER(text)
Arguments
text (required)
Text to convert to title case. Can be text string, cell reference, or formula result.
Text string: =PROPER("john smith")
Cell reference: =PROPER(A1)
Formula result: =PROPER(TRIM(A1))
Concatenation: =PROPER(A1&" "&B1)
Common Patterns
Clean Then Title Case
=PROPER(TRIM(A2))
Remove extra spaces first
Full Name from Parts
=PROPER(A2&" "&B2)
Combine first + last, title case
Address Formatting
=PROPER(A2)&", "&PROPER(B2)&", "&UPPER(C2)
Street (title), City (title), State (caps)
With Conditional
=IF(A2<>"", PROPER(A2), "")
Only convert if not empty
What PROPER Changes vs Preserves
✓ Changes:
- • A-Z letters
- • Accented characters (É, ñ, ü)
- • International letters
✓ Preserves:
- • Numbers (123)
- • Spaces
- • Punctuation (!@#$%)
- • Special characters (©®™)
Workarounds for Special Names
McDonald Fix with SUBSTITUTE
Manual replacement for known exceptions
O'Neill Fix
Fix unwanted capitalization after apostrophe
Multiple Substitutions
Chain multiple fixes together
⚠️ Important Notes
- • Returns new text: Doesn't modify original cell
- • Case insensitive input: Works same on "JOHN", "john", "JoHn"
- • Apostrophe handling: Capitalizes after ' (o'brien → O'Brien)
- • Number boundaries: Treats numbers as word breaks
- • No smart exceptions: Capitalizes every word (including 'the', 'and', 'of')
- • Copy Paste Values: Use Paste Values to replace original after conversion
- • Performance: Fast even on 100k+ rows
10+ PROPER Examples
Basic Name Formatting
1. Fix ALL CAPS Names
=PROPER(A2)
"JOHN SMITH" → "John Smith"
2. Fix Lowercase Names
=PROPER(A2)
"jane doe" → "Jane Doe"
3. Combine First + Last Name
=PROPER(A2&" "&B2)
Merges and title cases both names
Address Formatting
4. Street Address
=PROPER(A2)
"123 main street" → "123 Main Street"
5. City Name
=PROPER(A2)
"new york" → "New York"
6. Full Address
=PROPER(A2)&", "&PROPER(B2)&", "&UPPER(C2)
Street (title), City (title), State (caps)
Data Cleanup
7. Clean + Title Case
=PROPER(TRIM(A2))
Remove extra spaces, then title case
8. Job Title Formatting
=PROPER(A2)
"marketing manager" → "Marketing Manager"
9. Company Name
=PROPER(A2)
"acme corporation" → "Acme Corporation"
Advanced Applications
10. Conditional Formatting
=IF(A2<>"", PROPER(A2), "")
Only convert if cell not empty
11. Fix McDonald Names
=SUBSTITUTE(PROPER(A2), "Mcdonald", "McDonald")
Manual fix for special name patterns
🎯 Pro Tips
- • Always clean first: Use TRIM before PROPER to remove extra spaces
- • Copy Paste Values: Replace original data after conversion
- • Accept 95% accuracy: Good enough for bulk data cleanup
- • Manual fixes: Use Find & Replace for McDonald, O'Brien, etc.
- • Power Query alternative: Transform → Format → Capitalize Each Word
- • Flash Fill option: Type examples, Excel may auto-detect pattern
Frequently Asked Questions
Why doesn't PROPER handle McDonald correctly?
PROPER only capitalizes first letter after word boundaries (spaces, apostrophes, numbers). It doesn't recognize 'Mc' or 'Mac' as special prefixes. PROPER('mcdonald') = 'Mcdonald'. Fix with SUBSTITUTE: =SUBSTITUTE(PROPER(A1), 'Mcdonald', 'McDonald'). For bulk data, accept 95% accuracy and manually fix special cases.
What happens with apostrophes in PROPER?
Apostrophes trigger new word capitalization. o'brien → O'Brien (correct), can't → Can'T (incorrect 'T'). This works well for Irish names (O'Neill, O'Connor) but fails for contractions. For contractions, use SUBSTITUTE to fix: =SUBSTITUTE(PROPER(A1), ''T', ''t').
Does PROPER handle accented characters?
Yes, PROPER works with all Unicode letters: café → Café, münchen → München, josé → José. Handles international names correctly for capitalization. Numbers, spaces, and punctuation still treated as word boundaries regardless of character set.
Can I make PROPER skip certain words like 'and' or 'the'?
No, PROPER has no exceptions - it capitalizes every word. 'lord of the rings' → 'Lord Of The Rings'. For title case with grammar rules, need custom formula with nested SUBSTITUTE or use Power Query with custom transformations. For most business data, PROPER's approach is acceptable.
How do I convert permanently without formulas?
Three methods: (1) Use PROPER formula, copy results, Paste Values over original, (2) Power Query: Transform → Format → Capitalize Each Word, (3) Flash Fill: type example in next column, Ctrl+E to auto-fill. Method 1 is quickest for one-time cleanup.
What's the difference between PROPER, UPPER, and LOWER?
UPPER: ALL CAPITALS. LOWER: all lowercase. PROPER: Title Case (first letter capital). Use UPPER for codes/acronyms (SKU, USA), LOWER for emails/URLs, PROPER for names/titles. All three preserve numbers and punctuation, only affect letters.