Excel Practice
Lessons Lesson 63

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

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

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

The first row was typed with caps lock on. In B2, put it into ordinary name capitals.

To begin, type it exactly:

=PROPER(A2)

B2
Row A B C D E F G
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. 1

    The first row was typed with caps lock on. In B2, put it into ordinary name capitals. To begin, type it exactly: =PROPER(A2).

    Hint. One argument.

  2. 2

    Here is where it goes wrong. Read =PROPER(A3) and say exactly what it returns for the surname mcdonald.

    Hint. Apply the rule literally and read the result.

  3. 3

    One it happens to get right. Read =PROPER(A5) and say what it returns for o'brien.

    Hint. Where does the word break fall?

  4. 4

    Codes are a different matter. In B6, put the reference in A6 into capitals.

    Hint. One argument.

  5. 5

    In B5, make the Dutch surname in A4 all lower case. Use a function instead of retyping it.

    Hint. The opposite of UPPER.