PROPER

returns text with words in lowercase after a capitalised first letter.

Syntax:

PROPER(text)

returns text with the first letter of each word capitalised, and other letters in lower case. More specifically, the first letter and any letter that follows a non-letter are capitalised.

Example:

PROPER("gooD morNINg")

returns Good Morning.

PROPER("john o'connor")

returns John O'Connor.

PROPER("don't worry")

returns Don'T Worry. The T is capitalised as it follows a non-letter.

SUBSTITUTE(PROPER(SUBSTITUTE("don't worry","'","'x")),"'X","'")

returns Don't Worry.

Application:

Let's consider a scenario where we have a list of product names that have been entered inconsistently. We want to clean up this data so that the first letter of each word is capitalized and the rest are in lowercase. This is a perfect use case for the PROPER function.

Table: Products

	Original Product Name	Desired Format	Result using PROPER
	A	B	C
1	ultra gaming mouse x1	Ultra Gaming Mouse X1	Ultra Gaming Mouse X1
2	advanced keyboard mk-2	Advanced Keyboard Mk-2	Advanced Keyboard Mk-2
3	super monitor 3000	Super Monitor 3000	Super Monitor 3000
4	ergonomic office chair pro	Ergonomic Office Chair Pro	Ergonomic Office Chair Pro
5	wireless headphones plus	Wireless Headphones Plus	Wireless Headphones Plus
6	portable speaker v5	Portable Speaker V5	Portable Speaker V5

The PROPER function takes a text string as its argument and returns a new string where the first letter of each word is converted to uppercase, and all other letters are converted to lowercase.

Consider the first row in the table:

Original Product Name: "ultra gaming mouse x1"
PROPER Function: PROPER("ultra gaming mouse x1")

Here's how the function processes this string:

It identifies each word: The function looks for spaces to determine the boundaries of each word. In this case, the words are "ultra," "gaming," "mouse," and "x1."
It capitalizes the first letter of each word:
- "ultra" becomes "Ultra"
- "gaming" becomes "Gaming"
- "mouse" becomes "Mouse"
- "x1" becomes "X1"
It converts the remaining letters to lowercase: All subsequent letters in each word are converted to lowercase. For "Ultra," "gaming," and "mouse," this doesn't change anything, but if the original had been "ULTRA," it would still be converted to "Ultra." The "x" in "x1" is also capitalized, and the "1" remains as is.
It combines the processed words: The function then puts the words back together with the original spacing, resulting in "Ultra Gaming Mouse X1".

The same process is applied to every other row in the table:

For "advanced keyboard mk-2", the function identifies the words "advanced," "keyboard," and "mk-2." It capitalizes the "A," "K," and "M," resulting in "Advanced Keyboard Mk-2".
For "super monitor 3000", the words are "super," "monitor," and "3000." The PROPER function capitalizes the "S" and "M," giving "Super Monitor 3000".
And so on for all the other examples.