UPPER, LOWER and PROPER

About this lesson

UPPER turns text into capitals. LOWER turns it into small letters. PROPER capitalises the first letter of every word and lowers the rest.

By the end you can

change the case of text with UPPER, LOWER and PROPER.
say what PROPER gets wrong on names like McKay.

Practises UPPER LOWER PROPER TRIM

By Ali, Founder, Excel Lessons

The idea

Two of these are safe and one is not. UPPER and LOWER apply the same change to every character, so there is nothing for them to get wrong. That makes them the right tool before comparing two lists from different systems, where one is in capitals and the other is not.

PROPER is a rule about letters pretending to be knowledge about names. It capitalises after every non-letter. That produces Mcdonald, Macleod, Van Der Berg and De Souza, and every one of them is somebody's name spelled wrong. It gets O'Brien right, but by luck. The same rule that puts the capital after the apostrophe refuses to put one in the middle of McDonald. On a list of customers this is worse than leaving the text alone. A name that was only inconsistent is now wrong with confidence. Use it on descriptions and place names. Check it by eye on anything that gets printed on a letter.

The mistake to watch for

Running PROPER over a column of names. It capitalises after every non-letter and nothing else. So McDonald, MacLeod, van der Berg and de Souza all come out wrong. Each is now a customer's name spelled wrong on a letter. Use PROPER on descriptions and place names. On names, check every result by eye, or leave the text alone.

Where this comes up again

Why VLOOKUP Returns #N/A Errors and Robust Formulas
LEN: Counting Characters Text: Extracting
Splitting Text: Putting It Together Text: Splitting

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

PROPER capitalises the first letter of each word.

In `B2`, type:

=PROPER(A2)

Row	A	B
1	As entered	Tidied
2	ANNA-MARIE FOLEY
3	mcdonald
4	van der berg
5	o'brien
6	gb-2024-0031
7
8

Every step

1

PROPER capitalises the first letter of each word. In B2, type: =PROPER(A2).

Hint. PROPER takes a cell.
2

PROPER treats every word the same, with no exceptions. Read =PROPER(A3) and say what it returns for mcdonald.

Hint. Use the rule just as it is, then read it.
3

PROPER also capitalises after a hyphen or apostrophe. Read =PROPER(A5) and say what it returns for o'brien.

Hint. Where does the word break fall?
4

UPPER turns text into capital letters. In B6, capitalise the code in A6 with UPPER.

Hint. UPPER takes a cell.
5

LOWER turns text into small letters. In B5, make A4 lower case, using LOWER. Use a function instead of retyping it.

Hint. The opposite of UPPER.