Excel track · Exercise 64 of 86 Paid
UPPER, LOWER and PROPER
Normalise capitalisation with UPPER, LOWER and PROPER, and know which one a code needs.
Intermediate
References and names in mixed case
Read-only data · screen 1 of 2 · answer in B7
In B7, put the reference in A2 into capitals so it matches the shipping labels.
| A | B | |
|---|---|---|
| 1 | Reference | Status |
| 2 | ab-2291-xk | open |
| 3 | cd-1180-qt | closed |
| 4 | ef-4472-mp | open |
| 5 | gh-9031-lr | open |
| 6 | ij-6620-bn | closed |
| 7 | Label |
In B7, tidy the name in A2 so each word starts with a capital and the rest is lower case.
| A | B | |
|---|---|---|
| 1 | Name | Team |
| 2 | ada LOVELACE | engine |
| 3 | GRACE hopper | research |
| 4 | alan TURING | engine |
| 5 | katherine JOHNSON | research |
| 6 | mary jackson | ops |
| 7 | Tidy name |
Reference answer
What it does
UPPER makes every letter a capital, LOWER takes them all down, and PROPER capitalises the first letter of each word. Each takes one piece of text and returns new text. Digits, hyphens and spaces pass through untouched, and none of the three removes a space or fixes a spelling.
The first screen asks for ab-2291-xk as a shipping label, which is AB-2291-XK. UPPER is the only one of the three that gives that. PROPER returns Ab-2291-Xk, because it starts a new word after every hyphen.
The second screen has ada LOVELACE, and PROPER returns Ada Lovelace. The change runs one way: once UPPER has run, the original case is gone. Keep the source column when the spelling may matter later.
Syntax
=UPPER(text) · =LOWER(text) · =PROPER(text)Worked example
On References and names in mixed case, =UPPER(A2) returns AB-2291-XK.
=UPPER(A2)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The converted text was used in arithmetic, as in =UPPER(A2)/0. |
#NAME? | UPPER was typed with an extra letter, and Excel returns #NAME?. |
Half converted | PROPER on a code starts a new word after each hyphen, so the label comes out Ab-2291-Xk. |
At work
A sheet that pulls names and codes from elsewhere, where the capitals come out inconsistent.