Customer databases rarely arrive clean. Names come in all capitals, all lowercase, or mixed inconsistently — and manually correcting each one wastes hours. The PROPER function fixes this in seconds. It capitalises the first letter of every word in a text string and lowercases everything else. One formula applied to a whole column transforms "JAMES SMITH" into "James Smith" and "sarah jones" into "Sarah Jones" automatically.
PROPER is not a perfect solution for every case — it has specific limitations with surnames like McBride and acronyms like NATO. This guide covers the function in full: what it does well, where it falls short, and the workarounds that fix each limitation.
What Is the PROPER Function Syntax?
PROPER takes a single argument — the text to convert.
| Argument | Required? | What it does |
|---|---|---|
| text | Required | The text string to convert to proper case. Can be a cell reference, a hardcoded string in double quotes, or a formula that returns a text string. Numbers are not affected — they pass through unchanged. |
PROPER capitalises the first character after any space, hyphen, or apostrophe. All other characters are lowercased. Consequently, it handles hyphenated names like "Smith-Jones" and "O'Brien" correctly — but creates issues with prefixes like "Mc" and acronyms, which are covered in the limitations section below.
UPPER, LOWER, and PROPER — Which One to Use?
Excel has three text-case functions. Each one serves a different purpose. Understanding them together makes it easier to choose the right one for each task.
| Function | What it produces | Input → Output | Best for |
|---|---|---|---|
| UPPER | All letters capitalised | "james smith" → "JAMES SMITH" | Reference codes, IDs, headers, database keys that must be consistent for lookups |
| LOWER | All letters lowercased | "JAMES SMITH" → "james smith" | Email addresses, usernames, case-insensitive comparisons, preparing data for VLOOKUP |
| PROPER | First letter of each word capitalised | "james smith" → "James Smith" | Customer names, product titles, city names, address fields — any displayable proper noun |
Examples 1–4: Core Use Cases
This is PROPER's most common use. Data imported from CRMs, web forms, or older databases often arrives in all caps, all lowercase, or randomly mixed. A single PROPER formula standardises the entire column.
Address fields suffer from the same inconsistency as name fields. PROPER fixes city names, street names, and county fields in one step. Hyphenated place names like "Stratford-upon-Avon" also capitalise correctly because PROPER treats the hyphen as a word boundary.
Product catalogues imported from suppliers often contain inconsistent capitalisation. PROPER standardises them quickly. Furthermore, combining PROPER with TRIM ensures that extra spaces from copy-paste operations do not carry through to the output.
Excel has no built-in sentence case function. However, you can build one using UPPER, LEFT, and LOWER. This converts only the very first character to uppercase and lowercases everything else — exactly the behaviour you need for email subject lines, notes fields, and single-sentence values.
What Are PROPER's Limitations?
PROPER is a simple pattern-based function. It capitalises the first letter after every space, hyphen, and apostrophe — without any awareness of language rules or specific names. This causes four known categories of errors.
Limitation 1: Mc and Mac Prefixes
"McBride" should have a capital B after the Mc — but PROPER converts it to "Mcbride". Similarly, "MacDonald" becomes "Macdonald". PROPER sees "Mc" as the beginning of one word and lowercases everything after the first letter. There is no way to teach it the Mc/Mac capitalisation rule natively.
Limitation 2: Acronyms and Abbreviations
PROPER converts "FIFA" to "Fifa", "NASA" to "Nasa", and "USB" to "Usb". It has no knowledge of which words are acronyms. Consequently, any known acronym in your data will be incorrectly lowercased after PROPER runs.
Limitation 3: Apostrophes Trigger Capitalisation
PROPER treats every apostrophe as a word boundary. This correctly handles "O'Brien" → "O'Brien". However, it also wrongly capitalises letters after possessive apostrophes: "it's" becomes "It'S", and "don't" becomes "Don'T". This matters when PROPER is applied to notes or free-text fields rather than name fields.
Limitation 4: Numbers with Preceding Letters
PROPER capitalises the character after any digit if the next character is a letter. So "3g network" becomes "3G Network" and "4k display" becomes "4K Display". This may or may not be the intended result depending on your data.
How to Fix PROPER's Known Limitations
Fix 1: SUBSTITUTE to Restore Known Acronyms
The most reliable workaround for acronyms is to apply PROPER first and then use SUBSTITUTE to restore each known acronym to its correct form. Each SUBSTITUTE call wraps the previous one — so you can chain as many corrections as needed.
Fix 2: Flash Fill for One-Time Conversions
Flash Fill (Ctrl+E) is a fast no-formula alternative when you need to convert a column once and the data has consistent exceptions. Type two or three examples of the correctly formatted names in the adjacent column. Then press Ctrl+E and Excel fills the rest automatically, matching the pattern you demonstrated. Flash Fill handles Mc and Mac prefixes correctly if you provide enough examples for it to detect the pattern.
Examples 5–8: Advanced Combinations
Data exported from legacy systems often contains non-printable characters (line breaks, tab characters) alongside extra spaces. CLEAN removes non-printable characters. TRIM removes extra whitespace. PROPER then capitalises the cleaned result.
Sometimes you want to capitalise selectively — for example, only when the source cell is all uppercase, or only when a record has a specific status. Wrapping PROPER in IF applies the conversion conditionally without affecting other rows.
When first name and last name are in separate columns, combining PROPER on each before joining them produces a clean full name regardless of how the source columns were entered. This is especially useful before generating personalised documents or mail-merge outputs.
Common PROPER Issues and How to Fix Them
Name still shows with wrong capitalisation after PROPER
Check whether the cell contains extra spaces — a hidden space before a letter causes PROPER to capitalise that letter unexpectedly. Wrap with TRIM to remove all extra whitespace before applying PROPER. Additionally, check whether the name contains a known exception like Mc, Mac, or an acronym, and use SUBSTITUTE to fix it afterwards.
PROPER returns a number unchanged
PROPER only affects text characters. If the cell contains a genuine number (not text formatted as a number), PROPER passes it through unchanged. This is expected behaviour and is not an error. Numeric cells do not need case conversion.
Apostrophe causes a letter to be wrongly capitalised
PROPER capitalises the character after any apostrophe. This affects contractions like "it's" → "It'S" and possessives like "the company's" → "The Company'S". For this reason, PROPER should only be applied to name and title fields — not to free-text notes or sentence-level content.
Frequently Asked Questions
-
What does the PROPER function do in Excel?PROPER converts text so that the first letter of every word is uppercase and all remaining letters are lowercase. For example, =PROPER("james smith") returns "James Smith", and =PROPER("SARAH JONES") returns "Sarah Jones". It treats spaces, hyphens, and apostrophes as word boundaries, so hyphenated names like "Smith-Jones" become "Smith-Jones" with both initials capitalised.
-
Why does PROPER return "Mcbride" instead of "McBride"?PROPER applies a simple rule — capitalise after spaces, hyphens, and apostrophes — without any knowledge of specific naming conventions. The "Mc" and "Mac" prefix rule (which requires a capital on the letter after the prefix) is a linguistic convention that PROPER does not know. The fix is to apply PROPER first and then use SUBSTITUTE to restore the correct capitalisation: =SUBSTITUTE(PROPER(A2), "Mcb", "McB") turns "Mcbride" into "McBride".
-
How do I fix acronyms after using PROPER?Wrap the PROPER result in nested SUBSTITUTE calls, one for each known acronym. For example: =SUBSTITUTE(SUBSTITUTE(PROPER(A2), "Fifa", "FIFA"), "Usb", "USB"). Each SUBSTITUTE finds the incorrectly lowercased version that PROPER produced and replaces it with the correct all-caps form. This approach works for any finite list of known acronyms in your dataset.
More Questions About PROPER
-
Does Excel have a sentence case function?No — Excel does not have a built-in sentence case function. However, you can build one with a combination of other functions: =UPPER(LEFT(A2,1)) & LOWER(MID(A2, 2, LEN(A2))). This extracts the first character and capitalises it, then lowercases everything from character 2 onwards. The result is sentence case — only the first word of the string is capitalised. Add TRIM inside for extra safety: =UPPER(LEFT(TRIM(A2),1)) & LOWER(MID(TRIM(A2),2,LEN(TRIM(A2)))).
-
What is the difference between PROPER and title case?PROPER capitalises the first letter of every word without exception — including short words like "a", "an", "the", "of", and "in". True title case (as used in book and article titles) leaves minor words lowercase unless they are the first or last word. Excel's PROPER function does not distinguish between major and minor words. If you need true title case for editorial or publishing purposes, the output of PROPER will require manual review for short connector words.
-
Can I use Flash Fill instead of PROPER?Yes, Flash Fill (Ctrl+E) is a good alternative for one-time conversions. Type two or three correctly capitalised names in the adjacent column, then press Ctrl+E. Excel detects the pattern and fills the rest automatically. Flash Fill also handles Mc and Mac exceptions if you provide enough examples. However, Flash Fill produces static values — it will not update if the source data changes. Use PROPER when you need a formula that auto-updates; use Flash Fill when the data import is a one-off task.