--- title: "TRIM, CLEAN and T in Google Sheets: clean up text" canonical: "https://www.owox.com/blog/articles/trim-clean-t-functions-google-sheets" updated: "2026-10-05" --- # TRIM, CLEAN and T functions in Google Sheets: remove extra spaces and hidden characters TRIM removes the spaces at the start and end of a text and reduces repeated spaces inside it to one. CLEAN removes line breaks, tabs and other non-printable characters. T returns a value only if it is text. This guide shows how to apply them to one cell and to a whole range, what each of them leaves behind, such as the non-breaking space, and how to combine them. [Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 9 min read ![TRIM, CLEAN and T functions in Google Sheets: remove extra spaces and hidden characters](https://cdn.owox.ai/www/webflow/6a142a1891fc4829fe01a233_Optimizing-Your-Data-with-TRIM-CLEAN-and-T-Functions.png/public) To remove the extra spaces from a cell, wrap it in TRIM: > `=TRIM(C3)` TRIM deletes the spaces at the start and at the end of the text and reduces every run of spaces inside it to a single one. For a whole range, put it inside ARRAYFORMULA: > `=ARRAYFORMULA(TRIM(C3:C12))` TRIM has two companions for cleaning text: | Function | What it does | What it leaves | | --- | --- | --- | | `TRIM(text)` | Removes leading, trailing and repeated spaces | Line breaks, tabs, non-breaking spaces | | `CLEAN(text)` | Removes line breaks, tabs and other non-printable ASCII characters | Ordinary spaces, non-breaking spaces, zero-width spaces | | `T(value)` | Returns the value if it is text, and an empty text if it is not | Nothing: it does not change the text | The usual clean-up of imported text is the first two together: `=TRIM(CLEAN(C3))`. ## TRIM: remove extra spaces > `=TRIM(text)` * **text:** the text, or a reference to the cell that holds it. > `=TRIM(C3)` ![Google Sheets with product names that have extra spaces in column C and the formula =TRIM(C3) returning them with single spaces in column E](https://cdn.owox.ai/www/webflow/68ee25f34fc53e9db5086999_1.-Example-of-TRIM.png/public) “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](/blog/articles/vlookup-in-google-sheets) 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))` ![Google Sheets with the formula =ARRAYFORMULA(TRIM(C3:C12)) in E3 returning the trimmed product name for every row from one formula](https://cdn.owox.ai/www/webflow/68ee2714fa7f974c1517d8ee_6.1-ARRAYFORMULA-with-TRIM-and-CLEAN-Functions.png/public) See [array formulas](/blog/articles/array-formulas-google-sheets) 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)` ![Google Sheets with customer feedback that contains line breaks in column C and the formula =CLEAN(C3) returning each comment on one line in column E](https://cdn.owox.ai/www/webflow/68ee261efd60a5a2b843bb7c_2.-Example-of-CLEAN.png/public) 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)](/blog/articles/char-function-google-sheets), 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))` ![Google Sheets with the formula =ARRAYFORMULA(CLEAN(D16:D26)) returning the feedback column without line breaks from one formula](https://cdn.owox.ai/www/webflow/68ee2756f5d6717da76c08d8_6.2-ARRAYFORMULA-with-TRIM-and-CLEAN-Functions.png/public) Like TRIM, CLEAN returns text whatever goes in. ## The Turning Point OWOX Data Marts See your first report built in real time. _15 minutes._ 1. Connect your data warehouse 2. Pick your metrics 3. Get a live Google Sheets report In the time it takes to write a ticket. Then imagine never writing that ticket again. [Book a Demo](/book-a-call) We'll use your actual use case ## 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](/blog/articles/substitute-function-google-sheets), 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+", " "))` ![Google Sheets with the formula =TRIM(REGEXREPLACE(SUBSTITUTE(C3, CHAR(160), “ “), “\\s+”, “ “)) returning feedback text on one line with single spaces](https://cdn.owox.ai/www/webflow/68ee2a80c67b83a3c1645b4b_11.-TRIM-REGEXREPLACE-and-SUBSTITUTE.png/public) 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)` ![Google Sheets with a column that mixes product names and order numbers and the formula =T(B3) returning the names and leaving the cells for the numbers empty](https://cdn.owox.ai/www/webflow/68ee26c73844aeca4a86f642_5.-Filtering-Text-Values-Using-the-T-Function.png/public) On a column of numbers the result is empty throughout: ![Google Sheets with prices in column C and the formula =T(C3) returning empty cells, because none of the values is text](https://cdn.owox.ai/www/webflow/68ee267dfd60a5a2b843d9e9_4.-Using-T-Function-for-Handling-Numbers-and-Booleans.png/public) 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")` ![Google Sheets with a raw data column of texts and empty cells and the formula =IF(LEN(T(C3))>0, “Text Present”, “No Text”) labelling each row](https://cdn.owox.ai/www/webflow/68ee2b90fd60a5a2b8460c51_16.-Using-T-Function-with-IF-for-Text-Detection.png/public) `=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` ![Google Sheets with the Conditional format rules panel open, the range C3:C12, the custom formula =LEN(T(C3))>0 and the text cells highlighted in green](https://cdn.owox.ai/www/webflow/68ee2bdf4fc53e9db50b1e20_17.-Highlighting-Text-Using-LEN-and-T-Function.png/public) To list only the text values of a column, use [FILTER](/blog/articles/google-sheets-filter-function) 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, ",")))` ![Google Sheets with comma-separated categories in column D and the formula =ARRAYFORMULA(TRIM(SPLIT(D3:D12, “,”))) returning each category in its own cell without leading spaces](https://cdn.owox.ai/www/webflow/68ee27def64eaedfce773269_9.-ARRAYFORMULA-TRIM-and-SPLIT-for-Cleaner-Rows.png/public) FLATTEN stacks the pieces into one column: > `=ARRAYFORMULA(TRIM(FLATTEN(SPLIT(D3:D5, ","))))` ![Google Sheets with the formula =ARRAYFORMULA(TRIM(FLATTEN(SPLIT(D3:D5, “,”)))) returning all categories of three rows as one column](https://cdn.owox.ai/www/webflow/68ee2a33fa7f974c151994fb_10.-ARRAYFORMULA-TRIM-FLATTEN-and-SPLIT.png/public) More on splitting in [how to split cells](/blog/articles/how-to-split-cells-in-google-sheets). ### Trim before joining Trim the parts before joining them, so that the result has exactly the separator you typed: > `=CONCATENATE(TRIM(B3), " ", TRIM(C3))` ![Google Sheets with order IDs and product names and the formula =CONCATENATE(TRIM(B3), “ “, TRIM(C3)) returning the ID and the trimmed name with one space between them](https://cdn.owox.ai/www/webflow/68ee2aa88e33bb9d574faeee_12.-TRIM-and-CONCATENATE-to-Clean-and-Merge-Text.png/public) See [CONCATENATE and the ampersand](/blog/articles/concatenate-and-ampersand-google-sheets). ### Remove HTML tags or punctuation [REGEXREPLACE](/blog/articles/regex-functions-google-sheets) deletes what matches a pattern. Tags first, then the usual clean-up: > `=CLEAN(REGEXREPLACE(C3, "<[^>]+>", ""))` ![Google Sheets with raw values that contain HTML tags and the formula =CLEAN(REGEXREPLACE(C3, “<\[^>\]+>”, “”)) returning the values without the tags](https://cdn.owox.ai/www/webflow/68ee2782c67b83a3c162ad31_7.-Removing-HTML-Tags-with-CLEAN-and-REGEXREPLACE.png/public) Punctuation: > `=CLEAN(REGEXREPLACE(C3, "[[:punct:]]", ""))` ![Google Sheets with customer feedback and the formula =CLEAN(REGEXREPLACE(C3, “\[\[:punct:\]\]”, “”)) returning the comments without punctuation marks](https://cdn.owox.ai/www/webflow/68ee27adc67b83a3c162bbbb_8.-Eliminating-Punctuation-Using-CLEAN-and-REGEXREPLACE.png/public) ### Clean and fix the capitalization > `=PROPER(TRIM(CLEAN(SUBSTITUTE(C3, "-", " "))))` ![Google Sheets with the formula =PROPER(TRIM(CLEAN(SUBSTITUTE(C3, “-”, “ “)))) returning the feedback with every word capitalized](https://cdn.owox.ai/www/webflow/68ee2b408e33bb9d574fe2cb_14.-Data-Cleaning-with-CLEAN-TRIM-SUBSTITUTE-and-PROPER.png/public) PROPER capitalizes the letter after an apostrophe as well, which the screenshot shows in “Didn’T”. See [UPPER, LOWER and PROPER](/blog/articles/upper-lower-proper-functions-google-sheets). ### 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](/blog/articles/text-function-google-sheets), not T. **⚠️ Problem:** Two values look the same and still do not match. ✅ **Solution:** Compare their lengths with [LEN](/blog/articles/len-function-google-sheets). A difference points to an invisible character; [EXACT](/blog/articles/exact-function-google-sheets) 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](/blog/articles/substitute-function-google-sheets): Replace one text with another. * [REGEX functions](/blog/articles/regex-functions-google-sheets): Remove or extract by pattern. * [LEN](/blog/articles/len-function-google-sheets): Count characters to find hidden ones. * [CHAR](/blog/articles/char-function-google-sheets): Characters by their code. * [How to find and replace](/blog/articles/how-to-find-and-replace-google-sheets): Clean up without formulas. * [Remove duplicates](/blog/articles/remove-duplicates-google-sheets): The step after trimming. * [UPPER, LOWER and PROPER](/blog/articles/upper-lower-proper-functions-google-sheets): 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](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) 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. ![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/trim-clean-t-functions-google-sheets/screens/1.png/public) 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](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet. ## Your New Normal Turn your data into decisions. Governed data marts give you the clean foundation ML needs to actually work. * No AI hallucinations * Analyst-governed definitions * Every number traces to SQL [Get started free](https://www.owox.com/app-signup) ## FAQ ## Frequently Asked Questions Can I combine TRIM, CLEAN, and T functions in a single formula? Yes. =TRIM(CLEAN(A1)) removes line breaks and other non-printable characters first and then the extra spaces. T can be added as a guard, =TRIM(CLEAN(T(A1))), so that numbers and logical values return an empty text instead of being converted to text. How do I use the T function to filter text values in a range? T checks one value: it returns the value if it is text and an empty text otherwise. To list only the text values of a range, use FILTER with ISTEXT, for example =FILTER(B3:B12, ISTEXT(B3:B12)). Can the CLEAN function remove all unwanted characters from my data? No. CLEAN removes line breaks, tabs and other non-printable ASCII characters. It keeps ordinary spaces, non-breaking spaces (CHAR(160)), zero-width spaces, symbols and punctuation. Use TRIM for spaces, SUBSTITUTE for the non-breaking space and REGEXREPLACE for patterns. How does the TRIM function handle multiple spaces between words? TRIM reduces every run of spaces between words to a single space and removes the spaces at the start and the end of the text. It does not remove non-breaking spaces or line breaks. What are the best practices for cleaning data in Google Sheets? Use TRIM to remove extra spaces, CLEAN for non-printable characters, and SUBSTITUTE to replace unwanted text. Validate data consistently, remove duplicates with built-in tools, and use filters to identify errors. Always review data before analysis for accuracy and consistency. What are some common errors when using the CLEAN function? CLEAN does not remove ordinary or non-breaking spaces, it glues words together when the only thing between them was a line break, and it returns text, so a number that goes through CLEAN is no longer counted by SUM until it is converted back with VALUE. What are the key functions for cleaning data in Google Sheets? TRIM removes extra spaces, CLEAN removes non-printable characters, SUBSTITUTE and REGEXREPLACE replace or delete specific characters, and PROPER, UPPER and LOWER make capitalization consistent. The menu commands under Data > Data cleanup trim whitespace and remove duplicates without formulas. ## Who wrote this ![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public) [Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks. [LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko) [Google Sheets Tips](/blog/topics/google-sheets-tips) ## Related articles [Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](/blog/articles/unique-function-google-sheets) [Google Sheets Tips · GOOGLEFINANCE in Google Sheets: syntax and attributes list · October 2, 2026](/blog/articles/googlefinance-function-google-sheets) [Google Sheets Tips · QUERY function in Google Sheets: syntax, clauses, examples · October 2, 2026](/blog/articles/query-function-google-sheets) [See all articles →](/blog/articles) ## Book a call You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Links Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link. - [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md - [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md - [VLOOKUP](https://www.owox.com/blog/articles/vlookup-in-google-sheets) — /blog/articles/vlookup-in-google-sheets.md - [array formulas](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md - [CHAR(10)](https://www.owox.com/blog/articles/char-function-google-sheets) — /blog/articles/char-function-google-sheets.md - [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md - [SUBSTITUTE](https://www.owox.com/blog/articles/substitute-function-google-sheets) — /blog/articles/substitute-function-google-sheets.md - [FILTER](https://www.owox.com/blog/articles/google-sheets-filter-function) — /blog/articles/google-sheets-filter-function.md - [how to split cells](https://www.owox.com/blog/articles/how-to-split-cells-in-google-sheets) — /blog/articles/how-to-split-cells-in-google-sheets.md - [CONCATENATE and the ampersand](https://www.owox.com/blog/articles/concatenate-and-ampersand-google-sheets) — /blog/articles/concatenate-and-ampersand-google-sheets.md - [REGEXREPLACE](https://www.owox.com/blog/articles/regex-functions-google-sheets) — /blog/articles/regex-functions-google-sheets.md - [UPPER, LOWER and PROPER](https://www.owox.com/blog/articles/upper-lower-proper-functions-google-sheets) — /blog/articles/upper-lower-proper-functions-google-sheets.md - [TEXT](https://www.owox.com/blog/articles/text-function-google-sheets) — /blog/articles/text-function-google-sheets.md - [LEN](https://www.owox.com/blog/articles/len-function-google-sheets) — /blog/articles/len-function-google-sheets.md - [EXACT](https://www.owox.com/blog/articles/exact-function-google-sheets) — /blog/articles/exact-function-google-sheets.md - [How to find and replace](https://www.owox.com/blog/articles/how-to-find-and-replace-google-sheets) — /blog/articles/how-to-find-and-replace-google-sheets.md - [Remove duplicates](https://www.owox.com/blog/articles/remove-duplicates-google-sheets) — /blog/articles/remove-duplicates-google-sheets.md - [OWOX Data Marts](https://www.owox.com/app-signup) - [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md - [Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](https://www.owox.com/blog/articles/unique-function-google-sheets) — /blog/articles/unique-function-google-sheets.md - [Google Sheets Tips · GOOGLEFINANCE in Google Sheets: syntax and attributes list · October 2, 2026](https://www.owox.com/blog/articles/googlefinance-function-google-sheets) — /blog/articles/googlefinance-function-google-sheets.md - [Google Sheets Tips · QUERY function in Google Sheets: syntax, clauses, examples · October 2, 2026](https://www.owox.com/blog/articles/query-function-google-sheets) — /blog/articles/query-function-google-sheets.md - [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md