TRIM, PROPER and UPPER practice exercises

Tidy up text that came from somewhere else: strip extra spaces, and fix names and codes typed in the wrong case.

Start the first exercise

How TRIM, PROPER and UPPER work

TRIM, PROPER and UPPER each fix one thing in a text. TRIM removes extra spaces at the start and end and leaves a single space between words. UPPER makes every letter a capital, LOWER makes every letter small, and PROPER capitalizes the first letter of each word. Each takes one argument: the text, or the cell that holds it.

Names and codes pasted from other systems are rarely clean. If A2 holds " maria LOPEZ ", =PROPER(TRIM(A2)) returns "Maria Lopez". The functions sit inside each other: TRIM runs first, then PROPER fixes the capitals. =UPPER(TRIM(A2)) does the same for a code and returns it in capitals with no extra spaces.

These functions build a new text and leave the original alone. To replace the original, put the formula in a helper column, copy the result and paste it back as values. PROPER is not perfect either: "mcdonald" becomes "Mcdonald", so names like that need a manual fix. The exercises below cover removing extra spaces, fixing the case of names, cleaning a code with UPPER and TRIM, and fixing messy names with PROPER and TRIM.

  1. 1Remove extra spaces with TRIMIn B2, use TRIM to remove the extra spaces from the name in A2.
  2. 2Fix the case of names with PROPERPremiumIn B2, use PROPER to write the name in A2 with a capital first letter in each word.
  3. 3Clean a code with UPPER and TRIMPremiumIn B2, clean up the code in A2 so it has no extra spaces and is all capitals. Put TRIM inside UPPER.
  4. 4Fix messy names with PROPER and TRIMPremiumThe name in A2 has extra spaces and the wrong capitals. In B2, clean it up to "Maria Lopez". Put TRIM inside PROPER.

Functions in this topic

Read the guide

Questions

Why doesn't TRIM remove all the spaces in my cell?

TRIM only removes regular spaces. Text copied from web pages often contains non-breaking spaces, character 160, which TRIM leaves alone. Swap them first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). TRIM also keeps one space between words. To remove every space, use =SUBSTITUTE(A2," ","") instead.

How do I change text to uppercase or lowercase in Excel?

Use UPPER for capitals, LOWER for small letters and PROPER for a capital at the start of each word. For example, =LOWER(A2) turns "ABC-12" into "abc-12". The result is a new text, so write the formula in a helper column next to the original and copy the values back if you need to replace it.

How do I capitalize only the first letter of a sentence in Excel?

PROPER capitalizes every word, so it doesn't work for a sentence. Use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) instead. It makes the first letter a capital and the rest lowercase. Keep in mind that this also lowercases names and abbreviations in the middle of the sentence.