Why does TRIM still leave spaces between words?

TRIM only removes spaces at the start and end of a string. Any spaces that appear between words are considered internal and are left untouched. To collapse multiple internal spaces, combine TRIM with SUBSTITUTE or REGEXREPLACE.

Can TRIM be used on numbers or dates?

If you pass a numeric or date value, Excel automatically coerces it to text before trimming, which usually has no effect because numbers and dates contain no leading or trailing spaces. The function will still return a text representation of the original value.

Does TRIM work on arrays in Google Sheets?

Yes. In Google Sheets, TRIM can accept an array argument and will return an array of trimmed strings, preserving the original shape. This makes it handy for cleaning entire columns with a single formula.

What is the difference between TRIM and CLEAN?

TRIM removes only the space character (ASCII 32) from the beginning and end of a string. CLEAN removes non-printable characters (ASCII 0-31) from anywhere in the string. Use CLEAN when you suspect hidden control characters, and TRIM when you only need to tidy surrounding spaces.

TRIM function

TRIM removes all leading and trailing spaces from a text string, returning a clean version without extra blanks.

=TRIM(text)

Generate a TRIM formula

Describe what you need. The generator will reach for TRIM where TRIM is the right tool, and tell you when it is not.

Arguments

How TRIM reads its arguments

Argument	Required	Description
text	Required	A required argument of type text; if a non-text value is supplied it is coerced to text, and empty or error values are propagated.

Returns

A single text string with no leading or trailing spaces.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Clean a student name with extra blanks

Student	Subject	Assignment	Score	Max Score
Alice Smith	Math	Homework 1	85	100

=TRIM(A2)

Result: Alice Smith

Cell A2 contains the name " Alice Smith " with spaces before and after. TRIM strips those spaces, leaving the exact name "Alice Smith". The rest of the row is unchanged, demonstrating that TRIM works on a single cell without affecting other columns.

2. Prepare a subject label for lookup

Student	Subject	Assignment	Score	Max Score
Bob Jones	Science	Quiz 2	78	100

=TRIM(B3)

Result: Science

The subject entry in B3 is padded with spaces on both sides. Applying TRIM removes the leading and trailing blanks, producing the clean label "Science" that can be safely used in functions like VLOOKUP or XLOOKUP. Without trimming, a lookup would fail because the stored value would not match the exact lookup key.

3. Create a tidy assignment summary

Student	Subject	Assignment	Score	Max Score
Carol Lee	English	Project 1	92	100

=TRIM(CONCATENATE(C2," : ",D2," / ",E2))

Result: Project 1 : 92 / 100

CONCATENATE builds a string that includes the raw assignment name (which has surrounding spaces) together with the score and maximum. TRIM then removes the extra blanks at the beginning and end of the combined string, leaving a neatly formatted label. This is useful when you need to display or export readable summaries.

Common errors

Which TRIM error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	TRIM was given a multi-cell range (e.g., A2:A5) in a version of Excel that does not support dynamic arrays.	Apply TRIM to a single cell, or use the newer dynamic-array-compatible version of Excel that returns an array of trimmed values.
#N/A	The argument cell contains the #N/A error, often from a failed VLOOKUP or XLOOKUP.	Wrap the TRIM call in IFERROR or IFNA to supply a fallback value, or correct the lookup that produced #N/A.
#REF!	The reference in the TRIM argument points to a cell that has been deleted or moved, making the reference invalid.	Update the formula to point to a valid cell address or use a named range that remains stable.

Tips and when to use something else

Use TRIM before any text-matching functions (VLOOKUP, MATCH, XLOOKUP) to avoid mismatches caused by stray spaces.
TRIM does not affect internal spaces; if you need to collapse multiple spaces inside a string, combine it with SUBSTITUTE.
When you need to remove non-printing characters such as line breaks, use CLEAN after TRIM.
If you need to strip only leading spaces or only trailing spaces, consider the LEFT/RIGHT functions with FIND instead of TRIM.

Related functions

CONCATCONCAT joins two or more text strings into one continuous string, automatically coercing numbers and dates to text without a delimiter.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.

Frequently asked questions

Why does TRIM still leave spaces between words?: TRIM only removes spaces at the start and end of a string. Any spaces that appear between words are considered internal and are left untouched. To collapse multiple internal spaces, combine TRIM with SUBSTITUTE or REGEXREPLACE.
Can TRIM be used on numbers or dates?: If you pass a numeric or date value, Excel automatically coerces it to text before trimming, which usually has no effect because numbers and dates contain no leading or trailing spaces. The function will still return a text representation of the original value.
Does TRIM work on arrays in Google Sheets?: Yes. In Google Sheets, TRIM can accept an array argument and will return an array of trimmed strings, preserving the original shape. This makes it handy for cleaning entire columns with a single formula.
What is the difference between TRIM and CLEAN?: TRIM removes only the space character (ASCII 32) from the beginning and end of a string. CLEAN removes non-printable characters (ASCII 0-31) from anywhere in the string. Use CLEAN when you suspect hidden control characters, and TRIM when you only need to tidy surrounding spaces.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17