Cleaning text: TRIM & PROPER in Google Sheets
Imported data is often messy: extra spaces, ALL CAPS, random capitalization. TRIM removes leading and trailing spaces and collapses double spaces between words. PROPER capitalizes each word, while UPPER and LOWER change the whole text.
Nest them to clean in one step: =PROPER(TRIM(A2)). Clean data matters because lookups and COUNTIF treat "Ana" and "Ana " as different values.
Syntax
=TRIM(text) · =PROPER(text) · =UPPER(text) · =LOWER(text)
Example
=PROPER(TRIM(A2))
Turns " aNA lopez " into "Ana Lopez".
Common mistakes
- PROPER also changes names like "McDonald" to "Mcdonald".
- Non-breaking spaces from the web aren't removed by TRIM.
- Leaving the formulas in place: paste values once the data is clean.
← Joining text: & and TEXTJOIN · SUBSTITUTE & TEXT → · All lessons