TRIM() – Removes extra spaces from text

The TRIM() function removes leading and trailing spaces from a text string. It is useful for cleaning up data that may have inconsistent spacing.

Syntax

TRIM(text)

text: The text string or a cell reference containing the text from which to remove spaces.

Basic example

TRIM(T1A1)

Removes leading and trailing spaces from the text in cell T1A1.

If T1A1 = " Hello World " (with leading and trailing spaces): TRIM(T1A1) → "Hello World"
If T1A1 = "Spaced Out" (with multiple internal spaces but no leading/trailing ones): TRIM(T1A1) → "Spaced Out" (internal multiple spaces remain unchanged)
If T1A1 = "NoSpaces": TRIM(T1A1) → "NoSpaces" (no change if no leading/trailing spaces)
If T1A1 contains only spaces, e.g., " ": TRIM(T1A1) → "" (an empty string)

Key features

Leading/Trailing space removal: Specifically targets spaces at the beginning and end of the text.
Data cleaning: Helps in standardizing text data, especially when imported from other sources.
Preserves internal single spaces: Single spaces between words are preserved.

Practical uses

Cleaning user-inputted data in forms or cells.
Preparing text for comparisons, as extra spaces can cause logical comparisons to fail (e.g., "text" == " text " is false, but TRIM("text") == TRIM(" text ") would be true if both result in "text").
Ensuring consistency before concatenating strings or using them in lookups.

Common mistakes

Expecting removal of all spaces: TRIM() does not remove all spaces, only leading and trailing ones. Single internal spaces between words are preserved.
Confusing with SUBSTITUTE: To remove or replace all instances of spaces (or other characters), use the SUBSTITUTE() function.