Cleaning Spaces With TRIM

Data copied from websites, PDFs, or other systems often arrives with extra spaces. You might see a space before a name, two spaces between words, or trailing spaces you cannot even see.

These invisible spaces break lookups, sorting, and matching. The TRIM function cleans them up so your text behaves predictably.

TRIM removes spaces from text, following a simple rule:

• It deletes all spaces from the start and end
• It reduces any run of spaces between words to a single space

So TRIM(" Hello world ") returns the clean Hello world with exactly one space in the middle and none on the edges.

In practice you point TRIM at a messy cell. If A2 holds   John Smith   with stray spaces, this formula returns the tidy version:

The result is John Smith with no leading or trailing spaces. Put the formula in a new column, then copy and paste the values back over the original if you want to keep the clean data.

A common worry: does TRIM delete all spaces? No. It keeps single spaces between words because those are meaningful.

TRIM("New York") returns New York — the three spaces collapse to one. Your sentences and names stay readable; only the excess is removed.

A trailing space is invisible but real. The text Apple and Apple  (with a trailing space) look identical, yet a spreadsheet treats them as different values.

This causes lookups to fail and duplicates to slip through. Running TRIM on both columns before comparing them removes this hidden mismatch.

You can prove a cell has extra spaces by comparing its length before and after TRIM.

If LEN(A2) is larger than LEN(TRIM(A2)), the cell contains spaces that TRIM would remove. This formula returns how many extra space characters were lurking in the cell.

A frequent fix is to wrap a lookup value in TRIM so a stray space does not cause a missing match.

Here the search term in A2 is trimmed before VLOOKUP hunts for it in the table. Even if A2 had trailing spaces, the lookup now matches the clean value in the table.

TRIM only handles the regular space character. Text pulled from the web sometimes contains a non-breaking space — a different invisible character that TRIM ignores.

To remove those, first swap them for normal spaces with SUBSTITUTE using the character code 160, then apply TRIM. Combining the two clears almost any stubborn spacing.

Imported text can also hide non-printable characters like line breaks. The CLEAN function strips those out, while TRIM handles the spaces.

Using them together gives you fully scrubbed text. Apply CLEAN first to remove control characters, then TRIM to fix the spacing that remains.

TRIM works in both Excel and Google Sheets with the same goal. There is one nuance: Excel TRIM ignores non-breaking spaces, while Google Sheets TRIM also handles some of them.

For safety in either tool, the SUBSTITUTE-with-CHAR(160) trick is the reliable way to clean web-pasted text.

A TRIM formula shows clean text, but the original messy cell is still there. To replace it for good, copy the formula cells, then use Paste Special → Values over the original column.

This turns the formula results into plain text you can keep, and you can then delete the helper column. Now your data is permanently scrubbed, not just displayed clean.

Test your understanding of TRIM.

You learned to clean messy spacing in text.

• TRIM(text) removes leading and trailing spaces
• Internal runs of spaces collapse to a single space
• Trailing spaces silently break lookups and matches
• Use LEN minus LEN(TRIM()) to detect hidden spaces
• Pair with CLEAN and SUBSTITUTE(CHAR(160)) for web-pasted text

You have completed the text functions for extracting, measuring, and cleaning. Next you will learn to join and reshape text.