Cleaning text with TRIM and PROPER

1 / 5

These names were typed carelessly: extra spaces, and capitals in the wrong places. Some spaces are hard to see.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABC
1Name as typedClean name
2tARIQ li
3 NADIA pATEL
4 daniel fISCHER
5quinn EVANS
6 jamal nOVAK
7 isla kowalski
8
9
10
11
12
clean names
extra spaces
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How TRIM and PROPER works

  • Why does text need cleaning?

    Lists typed by people, or copied from other systems, are often messy. Messy spaces are also the most common reason a lookup can't find something that looks identical.

  • What do TRIM and PROPER do?

    • TRIM removes spaces at the start and the end, and turns double spaces in the middle into single ones.
    • PROPER gives each word a capital first letter and makes the rest lower case.

  • Can you use both at once?

    Yes, put one function inside another. Excel works from the inside out: =PROPER(TRIM(A2)) trims first, then fixes the capitals.

  • What about UPPER and LOWER?

    UPPER makes every letter a capital and LOWER makes them all lower case.

Know someone fighting with a spreadsheet? Send them this page.