Excel PROPER Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 70 of 105 · Full Excel course

The Excel PROPER function capitalises the first letter of every word in a text string and converts all other letters to lower case. It returns text and is the quickest way to tidy names, cities and product titles that were typed in inconsistent case. Because it treats any non-letter as a word boundary, it needs care with apostrophes, hyphens and acronyms.

Table of Contents

Syntax

=PROPER(text)

Arguments

Argument	Required / Optional	Meaning
text	Required	The text string, or a cell reference, to convert to proper case (also called title case).

How PROPER works

PROPER scans the text and capitalises any letter that follows a character which is not a letter: a space, digit, hyphen, apostrophe, full stop or the start of the string. Every other letter becomes lower case. That rule gives good results for “john smith” (John Smith) and “123 main street” (123 Main Street), but it also turns “o’neil” into O’Neil, “e-mail” into E-Mail, “McDonald” into Mcdonald and “NASA” into Nasa. Numbers and symbols pass through unchanged.

Step-by-step example: clean a customer list

Column A contains names in mixed case, such as ANITA sharma and ravi KUMAR with extra spaces.
In B2 enter =PROPER(TRIM(A2)). TRIM removes the surplus spaces and PROPER fixes the case, giving Anita Sharma and Ravi Kumar.
Fill the formula down to the end of the list.
Copy column B and paste as values over column A, then delete column B.
Scan the result for exceptions such as surnames beginning with Mc or Mac and correct them manually or with SUBSTITUTE.

Practical use cases

1. Title case for report headings

=PROPER(A2)

2. Fix names but keep a known acronym

=SUBSTITUTE(PROPER(A2),"Usa","USA")

PROPER runs first; SUBSTITUTE restores the acronym afterwards. Chain several SUBSTITUTE calls for more exceptions.

3. Combine first and last name columns in proper case

=PROPER(TRIM(A2)&" "&TRIM(B2))

4. Proper case for the first word only (sentence case)

=UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2)))

PROPER cannot do this because it capitalises every word; the LEFT and MID combination handles it.

5. Standardise city names before a lookup

=XLOOKUP(PROPER(TRIM(D2)),Cities[Name],Cities[Region])

Common mistakes and tips

Acronyms and brand names: PROPER lower-cases them. Keep a small exceptions table and apply SUBSTITUTE, or fix them manually.
Letters after apostrophes and hyphens: “it’s” becomes It’S. For plural possessives and contractions, replace the apostrophe with a placeholder, run PROPER, then restore it.
Applied to numbers or dates: the value is returned as text and stops behaving as a number.
Source not changed: PROPER returns a new value; paste as values to replace the original.
Whole ranges: in Excel 365 =PROPER(A2:A100) spills the results; older versions need the formula filled down.

PROPER compared with UPPER, LOWER and Flash Fill

UPPER and LOWER are unconditional: every letter changes. PROPER is rule-based and therefore the only one of the three that can produce unexpected output. When the data contains many exceptions, Flash Fill (Ctrl+E) can be a better option for a one-time clean-up: type the corrected version of the first two or three cells and Excel infers the pattern, including how you treated an acronym. Flash Fill produces static values, so use PROPER when the source data keeps changing and the result must update automatically. Power Query also offers Format, Capitalize Each Word, which follows the same rules as PROPER and suits repeated imports.

Related functions

UPPER: converts all letters to upper case.
LOWER: converts all letters to lower case.
TRIM: removes surplus spaces before PROPER runs.
SUBSTITUTE: restores acronyms that PROPER has changed.
See all lessons in the Excel Formulas course.

Frequently asked questions

Why does PROPER change McDonald to Mcdonald?

PROPER capitalises only the first letter after a non-letter character and lower-cases everything else. Names with internal capitals need a manual fix or a SUBSTITUTE step.