TRIM, CLEAN and T functions in Google Sheets: remove extra spaces and hidden characters

TRIM: remove extra spaces

=TRIM(text)

• text: the text, or a reference to the cell that holds it.

=TRIM(C3)

“Ergonomic Chair” becomes “Ergonomic Chair”. In our test, =TRIM(" a b ") returned “a b”, three characters.

Spaces matter more than they look. “Sarah “ with a space at the end is not equal to “Sarah”, so a VLOOKUP does not find it, COUNTIF counts it separately, and a pivot table gives it a row of its own.

Trim a whole range

TRIM takes one value. Given a range without ARRAYFORMULA, it returns the value of its own row at best, and #VALUE! when the formula sits outside the rows of the range. Wrapped in ARRAYFORMULA, one formula covers the column:

=ARRAYFORMULA(TRIM(C3:C12))

See array formulas for how such results expand.

Trim without a formula

To clean the cells in place, select the range and choose Data > Data cleanup > Trim whitespace. The values themselves are changed, so no helper column is needed. Like the function, the tool does not remove non-breaking spaces.

TRIM returns text

The result of TRIM is always a text, even when the cell held a number: =ISTEXT(TRIM(123)) returns TRUE. A number that went through TRIM is ignored by SUM. Convert it back with VALUE: =VALUE(TRIM(C3)).

CLEAN: remove line breaks and non-printable characters

=CLEAN(text)

• text: the text, or a reference to the cell that holds it.

=CLEAN(C3)

Non-printable characters arrive with exports and with text copied from other systems: line breaks inside a cell, tabs, control characters. They are invisible or look like a space, and they make two equal-looking values different. In our test CLEAN removed the line break, CHAR(10), and the tab, CHAR(9).

CLEAN deletes the character and puts nothing in its place, so two words separated only by a line break are glued together: “great!” and “but delivery was late.” become “great!but delivery was late.”. To get a space instead, replace the line break before cleaning:

=TRIM(CLEAN(SUBSTITUTE(C3, CHAR(10), " ")))

For a range:

=ARRAYFORMULA(CLEAN(D16:D26))

Like TRIM, CLEAN returns text whatever goes in.

What TRIM and CLEAN do not remove

Three characters survive both functions. All three results below come from our test sheet.

• The non-breaking space, CHAR(160). It comes from web pages and looks exactly like a space. =LEN(TRIM(" a" & CHAR(160) & " ")) returns 2: the ordinary spaces are gone, the non-breaking one stays. CLEAN leaves it too.
• The zero-width space, CHAR(8203). Invisible, and kept by CLEAN.
• The line break, for TRIM. =LEN(TRIM("a" & CHAR(10) & "b")) returns 3. Line breaks are the job of CLEAN.

Turn the non-breaking space into an ordinary one with SUBSTITUTE, then trim:

=TRIM(SUBSTITUTE(C3, CHAR(160), " "))

The longer form also collapses tabs and line breaks into single spaces with a regular expression:

=TRIM(REGEXREPLACE(SUBSTITUTE(C3, CHAR(160), " "), "\s+", " "))

To delete zero-width and non-breaking spaces outright:

=REGEXREPLACE(C3, "[\x{200B}\x{00A0}]", "")

To find out what an invisible character is, take it apart: =CODE(RIGHT(C3)) returns the code of the last character of the cell. 32 is a space, 160 a non-breaking space, 10 a line break.

T: keep only text

=T(value)

• value: the value to check.

T returns the value unchanged if it is text and an empty text if it is anything else. In our test =T("abc") returned “abc”, and =T(123) and =T(TRUE) returned an empty text.

=T(B3)

On a column of numbers the result is empty throughout:

T does not clean anything. Its use is as a guard: it lets text through and turns numbers and logical values into nothing, which is handy in front of a function that should only receive text.

Flag the cells that hold text

=IF(LEN(T(C3))>0, "Text Present", "No Text")

=ISTEXT(C3) answers the same question with TRUE or FALSE. As a custom formula in conditional formatting, either one highlights the text cells of a range:

=LEN(T(C3))>0

To list only the text values of a column, use FILTER with ISTEXT: =FILTER(B3:B12, ISTEXT(B3:B12)).

TRIM and CLEAN with other functions

Trim after splitting

Splitting “furniture, office, seating” by the comma leaves a space in front of every item but the first. TRIM around SPLIT removes it:

=ARRAYFORMULA(TRIM(SPLIT(D3:D12, ",")))

FLATTEN stacks the pieces into one column:

=ARRAYFORMULA(TRIM(FLATTEN(SPLIT(D3:D5, ","))))

More on splitting in how to split cells.

Trim before joining

Trim the parts before joining them, so that the result has exactly the separator you typed:

=CONCATENATE(TRIM(B3), " ", TRIM(C3))

See CONCATENATE and the ampersand.

Remove HTML tags or punctuation

REGEXREPLACE deletes what matches a pattern. Tags first, then the usual clean-up:

=CLEAN(REGEXREPLACE(C3, "<[^>]+>", ""))

Punctuation:

=CLEAN(REGEXREPLACE(C3, "[[:punct:]]", ""))

Clean and fix the capitalization

=PROPER(TRIM(CLEAN(SUBSTITUTE(C3, "-", " "))))

PROPER capitalizes the letter after an apostrophe as well, which the screenshot shows in “Didn’T”. See UPPER, LOWER and PROPER.

A lookup that ignores stray spaces

Trim the key and the search column inside the formula, without changing the data:

=ARRAYFORMULA(VLOOKUP(TRIM(F3), TRIM(B3:D12), 3, FALSE))

In our test this found “Sarah” for the key “Sarah “ with a space at the end. TRIM turns the whole range into text, so the value that comes back is text too, even from a column of numbers; wrap the formula in VALUE to calculate with it.

Problems and fixes

⚠️ Problem: TRIM was applied and the space is still there.

✅ Solution: It is a non-breaking space. Check with =CODE(RIGHT(C3)); if it returns 160, use =TRIM(SUBSTITUTE(C3, CHAR(160), " ")).

⚠️ Problem: The formula with a range returns one cell or #VALUE!.

✅ Solution: TRIM, CLEAN and T take one value. Wrap the formula in ARRAYFORMULA.

⚠️ Problem: Numbers stop adding up after the clean-up.

✅ Solution: TRIM and CLEAN return text. Wrap the result in VALUE, or apply them only to text columns.

⚠️ Problem: Words are glued together after CLEAN.

✅ Solution: The line break between them was removed. Replace CHAR(10) with a space first.

⚠️ Problem: T returns an empty cell.

✅ Solution: The value is a number, a date or TRUE/FALSE, not a text. To turn a number into text, use TEXT, not T.

⚠️ Problem: Two values look the same and still do not match.

✅ Solution: Compare their lengths with LEN. A difference points to an invisible character; EXACT confirms whether the two texts are identical.

⚠️ Problem: #REF! from an ARRAYFORMULA.

✅ Solution: The result has no room to expand. Clear the cells below the formula.

Related Google Sheets functions

• SUBSTITUTE: Replace one text with another.
• REGEX functions: Remove or extract by pattern.
• LEN: Count characters to find hidden ones.
• CHAR: Characters by their code.
• How to find and replace: Clean up without formulas.
• Remove duplicates: The step after trimming.
• UPPER, LOWER and PROPER: Consistent capitalization.

A column of TRIM formulas is a sign of where the data came from

Stray spaces and line breaks get into a sheet with exports and pasted text. A helper column of TRIM and CLEAN fixes this copy of the data; the next export brings the same spaces back, and the formulas have to be extended over it again.

The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

What changes for the person who maintains the sheet:

• Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
• The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
• No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. When the text is trimmed once in that SQL, the rows arrive clean, and TRIM is left for what people type into the sheet by hand. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.

Google Sheets TipsUNIQUE function in Google Sheets: syntax, examples and errorsOctober 2, 2026→Google Sheets TipsGOOGLEFINANCE in Google Sheets: syntax and attributes listOctober 2, 2026→Google Sheets TipsQUERY function in Google Sheets: syntax, clauses, examplesOctober 2, 2026→

See all articles →