UPPER & LOWER: Change Text Case (2025)
Quick Answer: UPPER converts to capitals: =UPPER('hello') returns 'HELLO'. LOWER to lowercase: =LOWER('HELLO') returns 'hello'. PROPER for title case: =PROPER('john smith') returns 'John Smith'. Use for data consistency.
What are UPPER, LOWER, and PROPER?
These three functions convert text case. Use them to standardize inconsistent text data, fix formatting issues, or prepare data for comparisons.
The Three Functions:
UPPER
ALL CAPS
=UPPER("hello")Returns: "HELLO"
LOWER
all lowercase
=LOWER("HELLO")Returns: "hello"
PROPER
Title Case
=PROPER("hello")Returns: "Hello"
Real-World Example:
| Original | UPPER | LOWER | PROPER |
|---|---|---|---|
| john SMITH | JOHN SMITH | john smith | John Smith |
| NEW york | NEW YORK | new york | New York |
| sku-123-ABC | SKU-123-ABC | sku-123-abc | Sku-123-Abc |
When to Use Each:
UPPER - Use For:
- • Product codes, SKUs, part numbers
- • Acronyms and abbreviations (USA, NASA)
- • Headers and section titles
- • Data standardization for matching
LOWER - Use For:
- • Email addresses (john@example.com)
- • URLs and web addresses
- • Usernames
- • Case-insensitive comparisons
PROPER - Use For:
- • Names (first, last, company)
- • Addresses (cities, streets)
- • Titles and headings
- • Professional formatting
PROPER Function Quirks:
⚠️ Capitalizes After Every Space
PROPER("o'brien") = "O'Brien" ✓
PROPER("mcdonald") = "Mcdonald" ✗ (not McDonald)
⚠️ Treats Numbers as Word Breaks
PROPER("address1main") = "Address1Main"
⚠️ Doesn't Handle Roman Numerals
PROPER("henry viii") = "Henry Viii" ✗ (not VIII)
💡 Common Use Cases
- • Data consistency: Standardize mixed-case imported data
- • Comparisons: Convert to same case before matching
- • Name cleanup: Fix ALL CAPS or all lowercase names
- • Email validation: Convert emails to lowercase
- • Professional formatting: Make documents look polished
UPPER, LOWER, PROPER Syntax
UPPER
=UPPER(text)
Converts all letters to uppercase. Numbers, spaces, punctuation unchanged.
LOWER
=LOWER(text)
Converts all letters to lowercase. Numbers, spaces, punctuation unchanged.
PROPER
=PROPER(text)
Capitalizes first letter of each word, lowercase for remaining letters.
Arguments
text (required)
Text to convert. Can be text string, cell reference, or formula result.
Text string: =UPPER("hello")
Cell reference: =LOWER(A1)
Formula result: =PROPER(TRIM(A1))
Common Patterns
Standardize for Comparison
=UPPER(A2)=UPPER(B2)
Case-insensitive match
Clean + Convert
=PROPER(TRIM(A2))
Remove spaces then title case
Email Lowercase
=LOWER(TRIM(A2))
Clean and lowercase email
Full Name Formatting
=PROPER(A2&" "&B2)
Combine first + last, title case
SKU Standardization
=UPPER(SUBSTITUTE(A2, " ", ""))
Remove spaces, convert to caps
What These Functions DON'T Change
✓ Preserved:
- • Numbers (123 stays 123)
- • Spaces
- • Punctuation (!@#$%)
- • Special characters (©®™)
✓ Only Changes:
- • A-Z letters
- • Accented characters (Ñ, é, ü)
- • International characters
⚠️ Important Notes
- • Returns new text: Doesn't modify original cell
- • Works with any language: Handles accented and international characters
- • Copy & Paste Values: Use Paste Values to replace original after conversion
- • PROPER quirks: Capitalizes after apostrophes, numbers, spaces
- • Empty cells: Returns empty string (not error)
- • Performance: Very fast, even on 100k+ rows
10+ Case Conversion Examples
Basic Conversions
1. Convert to All Caps
=UPPER(A2)
"hello world" → "HELLO WORLD"
2. Convert to Lowercase
=LOWER(A2)
"HELLO WORLD" → "hello world"
3. Convert to Title Case
=PROPER(A2)
"john smith" → "John Smith"
Data Cleaning
4. Clean Email Addresses
=LOWER(TRIM(A2))
" John@EXAMPLE.com " → "john@example.com"
5. Standardize Product Codes
=UPPER(SUBSTITUTE(TRIM(A2), " ", ""))
" sku 123 abc " → "SKU123ABC"
6. Fix ALL CAPS Names
=PROPER(A2)
"JOHN SMITH" → "John Smith"
Combined Operations
7. Full Name from First + Last
=PROPER(A2&" "&B2)
Combines and title cases first and last names
8. Username from Email
=LOWER(LEFT(A2, FIND("@", A2)-1))"John.Smith@example.com" → "john.smith"
9. Format City + State
=PROPER(A2)&", "&UPPER(B2)
"new york" + "ny" → "New York, NY"
Practical Applications
10. Case-Insensitive Comparison
=UPPER(A2)=UPPER(B2)
Compare ignoring case differences
11. Proper Names with Initials
=PROPER(A2)&" "&UPPER(LEFT(B2,1))&"."
"john" + "smith" → "John S."
🎯 Pro Tips
- • Copy & Paste Values: After conversion, Paste Values to replace original
- • Power Query: Transform → Format → Uppercase/Lowercase for bulk data
- • PROPER limitations: Doesn't handle McDonald, O'Brien correctly (capitalizes after apostrophe)
- • Email standard: Always lowercase for consistency
- • Case-insensitive search: Convert both sides to same case before comparing
- • Flash Fill alternative: Type examples, Excel may auto-detect pattern
Frequently Asked Questions
Why doesn't PROPER handle McDonald correctly?
PROPER capitalizes first letter after spaces, apostrophes, and numbers. 'mcdonald' becomes 'Mcdonald', 'o'brien' becomes 'O'Brien'. PROPER doesn't know naming conventions. For these cases, manually fix or use Find & Replace: find 'Mc' replace with 'Mc' (preserves case).
Can I convert case permanently without formulas?
Yes. Method 1: Use UPPER/LOWER/PROPER formula, copy results, Paste Values over original. Method 2: Power Query (Data → From Table) → Transform → Format → Uppercase/Lowercase/Capitalize Each Word. Method 3: Flash Fill - type example in next column, Excel detects pattern.
Do these functions work with accented characters?
Yes. UPPER('café') = 'CAFÉ', LOWER('MÜNCHEN') = 'münchen'. Works with all Unicode letters: ñ, é, ü, ø, etc. Also handles Cyrillic, Greek, and other alphabets. Numbers and punctuation unchanged.
How do I compare text ignoring case?
Convert both to same case: =UPPER(A1)=UPPER(B1) or =LOWER(A1)=LOWER(B1). Alternative: Use EXACT for case-sensitive, default = for case-insensitive. Excel's default comparison is case-insensitive, so 'Apple'='apple' returns TRUE without functions.
Why use LOWER for email addresses?
Email addresses are case-insensitive by standard (john@example.com = JOHN@example.com). Lowercase is convention for consistency and readability. Prevents duplicate entries (John@example.com vs john@example.com). Also standard for URLs, usernames.
Can I convert only first letter to uppercase?
Yes, combine functions: =UPPER(LEFT(A1,1))&LOWER(MID(A1,2,LEN(A1))). This capitalizes first letter only, rest lowercase. 'jOHN' becomes 'John' (PROPER would give 'John' too, but this handles single words better). Use PROPER for full names with spaces.