Confident Cells

TRIM: removing extra spaces and tidying text

20 November 2026 · 2 min read

=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)
AB
1Pasted nameTidy name
2 Nadia PatelNadia Patel
3Ella Bennett Ella Bennett
4Marek Ahmed Marek Ahmed
B2 holds =TRIM(A2), copied down. The spaces you can't see are gone.

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

Your turn

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.

AB
1Pasted nameTidy name
2 nadia patel
3ELLA BENNETT
4marek Ahmed
5 grace reid
6Tom OKAFOR