The TRIM function removes leading, trailing, and repeated regular spaces from text. It is useful for cleaning imported names, notes, email addresses, joined text, and street addresses.
TRIM leaves one regular space between words. Nonbreaking spaces, tabs, and line breaks need a different cleanup formula, covered below with tested examples.
TRIM syntax in Google Sheets
=TRIM(text)The text argument can be a text value or a cell reference. TRIM returns cleaned text and does not change the source cell.
Clean an entire column with TRIM
The sample table uses one shared column for the original five tasks: names, sentences, emails, joined names, and addresses.
In the input column below, each ␠ symbol represents one regular space so otherwise hidden padding remains visible.
| Task | Original input | Observed cleaned result |
|---|---|---|
| Name | ␠␠Maya␠␠␠Patel␠␠ | Maya Patel |
| Sentence | ␠␠Ready␠␠␠for␠␠review␠␠ | Ready for review |
␠␠maya␠.patel@example.com␠␠ | maya .patel@example.com | |
| Address | ␠␠24␠␠␠Cedar␠␠Road␠␠ | 24 Cedar Road |
| Joined name | Maya + Patel with padded source text | Maya Patel |
Enter this tested array formula in an empty result column. It cleans A2:A7 and leaves blank source rows blank.
=ARRAYFORMULA(IF(A2:A7="","",TRIM(A2:A7)))In the test sheet, the formula returned Maya Patel, Ready for review, maya .patel@example.com, and 24 Cedar Road for the first four rows.
The nonbreaking-space and line-break rows stayed unchanged. That difference matters because TRIM handles regular ASCII spaces, not every character that looks like whitespace.
Replace the original values after checking the results
Keep the source column while you check the cleaned results. Select the result cells, press Ctrl+C, select the destination’s first cell, and press Ctrl+Shift+V.
In the interface test, pasting B2:B5 into F2:F5 stored four text values without formulas: Maya Patel, Ready for review, maya .patel@example.com, and 24 Cedar Road.
Trim selected cells in place without a formula
Select the cells, then choose Data > Data cleanup > Trim whitespace. Google Sheets trims regular leading, trailing, and repeated spaces directly in the selected cells.
The tested two-cell selection produced the confirmation “Trimmed whitespace from 2 selected cells.” Keep a backup when you need to preserve the original text.
Join first and last names without extra spaces
Wrapping TRIM around joined text removes padding from the combined result and collapses repeated regular spaces between the names.
=TRIM(" Maya "&" "&" Patel ")The tested result was Maya Patel. This preserves the separator between words while removing the extra spaces around it.
Remove a nonbreaking space without joining words
Text copied from a web page or PDF can contain a nonbreaking space, represented by CHAR(160). TRIM alone does not remove it.
=TRIM(SUBSTITUTE(A6,CHAR(160)," "))This formula replaces the nonbreaking space with a regular space before TRIM runs. The tested result for Maya followed by a nonbreaking space and Patel was Maya Patel.
Replacing that character with an empty string would join the words. Use a regular space as the replacement when the hidden character separates words.
Handle tabs and line breaks without joining words
CLEAN removes nonprintable ASCII characters. When a line break separates two words, removing it can join them.
=TRIM(CLEAN(A7))The test cell contained Maya, a line break, and Patel. CLEAN removed the break, so the observed result was MayaPatel.
Replace separators with a regular space when you need to preserve the word boundary.
=TRIM(SUBSTITUTE(SUBSTITUTE(A7,CHAR(10)," "),CHAR(9)," "))This tested formula replaces line feeds and tabs with regular spaces, then trims the result. It returned Maya Patel.
Remove every regular space from an email address
TRIM cannot remove a single unwanted space inside text. The tested email still contained one space before the period after TRIM.
=SUBSTITUTE(A4," ","")The formula returned maya.patel@example.com. Use this only when no regular spaces should remain, such as in an email address or compact identifier.
Count how many characters TRIM removed
Compare the original length with the trimmed length to measure how many regular-space characters disappeared.
=LEN(A2)-LEN(TRIM(A2))For the tested padded name, the result was 6. The LEN guide explains how character counts work.
A blank input also returns an empty string with TRIM. The tested =TRIM(A8) result was blank.
Other Google Sheets articles you may also like