TRIM: removing extra spaces and tidying text
=TRIM(A2) takes out spaces at the start and end of text, and turns double spaces in the middle into single ones. It is the first thing to try on any list pasted from an email, a form or another system.
A worked example
A list of names was pasted from a sign-up form. Some have spaces before or after them, and one has two spaces in the middle. They look nearly right, but sorting, counting and lookups all trip over the spaces.
=TRIM(A2)
| A | B | |
|---|---|---|
| 1 | Pasted name | Tidy name |
| 2 | Nadia Patel | Nadia Patel |
| 3 | Ella Bennett | Ella Bennett |
| 4 | Marek Ahmed | Marek Ahmed |
Fixing capitals too: PROPER, UPPER, LOWER
Names typed in by people come in every style: nadia patel, ELLA BENNETT. Wrap TRIM in PROPER to give each word a capital letter:
=PROPER(TRIM(A2))
For postcodes and codes, use UPPER instead: =UPPER(TRIM(A2)). Watch PROPER with names like McDonald or O'Neil, which may need a hand fix.
Keeping the tidy version
The tidy names are formulas that point at the messy ones. To replace the originals, copy the tidy column, then use Paste Special, Values on top of the original column, and delete the formulas.
Common mistakes
TRIM doesn't remove the space
Text copied from web pages can contain a "non-breaking space", which TRIM ignores. Swap it for a normal space first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Line breaks in the text
Data from forms can have line breaks inside a cell. CLEAN removes them: =TRIM(CLEAN(A2)).
Numbers still won't add up
TRIM always gives text, even for " 125 ". Use =VALUE(TRIM(A2)) to get a number back (see numbers stored as text).
Try it now
These names were pasted from a sign-up form. In B2, take out the extra spaces and give each name a capital letter at the start of each word, like Nadia Patel. Your formula will be copied down to B6.
| A | B | |
|---|---|---|
| 1 | Pasted name | Tidy name |
| 2 | nadia patel | |
| 3 | ELLA BENNETT | |
| 4 | marek Ahmed | |
| 5 | grace reid | |
| 6 | Tom OKAFOR |