PROPER Google Sheets Function [With Quiz]
Updated: August 6, 2021 In Google Sheets the PROPER function outputs the provided text with each word capitalized.
PROPER Syntax
- text_to_capitalize = the "text" or a cell reference to the text you want to capitalize the first letter of every word (with all other letters made lowercase).
The definition of 'word' within the PROPER function is a consecutive series of letters [A-Za-z]. Anything that separates letters (punctuation or numbers) counts as the end of one word and the beginning of another.
For example, "mid-20th century" will become "Mid-20Th Century" because the "-20" makes "mid" the first word and "th" the second.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
PROPER Function Example
| A | B | C | |
| 1 | Text | Formula | Output |
| 2 | MICHAEL SCOTT | =PROPER(A2) | Michael Scott |
| 3 | jim halpert | =PROPER(A3) | Jim Halpert |
| 4 | DwIgHt ScHrUtE | =PROPER(A4) | Dwight Schrute |
| 5 | PAM beesly | =PROPER(A5) | Pam Beesly |
| 6 | Andy Bernard | =PROPER(A6) | Andy Bernard |
You can also reference text directly instead of using a cell reference:
Will output: Michael Scott
Using The PROPER Function On A Whole Column
By combining the PROPER function and the ArrayFormula function you can force PROPER to accept more than one argument.
This means you can capitalize multiple cells, an entire column or row, or even a table with one formula.
Here's an example using the same data as above:
| A | B | C | |
| 1 | Text | Formula | Output |
| 2 | MICHAEL SCOTT | =ArrayFormula(PROPER(A2:A6)) | Michael Scott |
| 3 | jim halpert | Jim Halpert | |
| 4 | DwIgHt ScHrUtE | Dwight Schrute | |
| 5 | PAM beesly | Pam Beesly | |
| 6 | Andy Bernard | Andy Bernard |
All five names are capitalized with only one formula.
PROPER Errors
The PROPER function is very stable.
It handles the following without error:
- Blank cells,
- Numbers (returning them as text), and
- Boolean data (returning them as text)
If you input a range (e.g. A2:A6) without using ArrayFormula it will output one piece of capitalized text if the range crosses the same row or column as the formula's cell.
Otherwise, it will throw this error:
An array value could not be found.
Potential Problems With PROPER
PROPER is great for fixing proper nouns (names of people and places) that have been entered incorrectly.
However, PROPER converts all characters not at the beginning of 'words' to lowercase.
ALL characters.
So names with bicapitalization (like Jerry DiCanio) and names that start with lowercase letters (like Wolf von Weyler) output incorrectly when using the PROPER function:
| A | B | C | |
| 1 | Text | Formula | Output |
| 2 | Jerry DiCanio | =PROPER(A2) | Jerry Dicanio |
| 3 | Wolf von Weyler | =PROPER(A3) | Wolf Von Weyler |
It's also not the best option for paragraphs or blocks of text as it does not capitalize sentences.
If you do need that functionality, I've written about it here.
PROPER Quiz
Here's a quick summary of how to use the quiz spreadsheet:
- Select the required cell while the formula bar is empty
- Type your answer in the formula bar
- You can click on the spreadsheet to add cell/range references
Complete all questions to solidify your understanding of the PROPER function.
| A | B | C | |
| 1 | Data | One | All |
| 2 | MICHAEL SCOTT | ||
| 3 | jim halpert | ||
| 4 | DwIgHt ScHrUtE | ||
| 5 | PAM beesly | ||
| 6 | Andy Bernard | ||
| 7 | |||
| 8 |
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
![MAXA Google Sheets Function [With Quiz]](https://kierandixon.com/wp-content/uploads/maxa-function-google-sheets.png)
