Text & Google Sheets cleanup · 5 min read

TRIM Not Working? Remove Hidden Spaces in Excel and Sheets

If TRIM leaves a visible gap or a lookup still fails after cleanup, the cell may contain a nonbreaking space rather than an ordinary space. Identify the character and choose whether internal spacing should change.

JHSOFT editorialUpdated September 9, 2026

WORKED EXAMPLESynthetic data

row_id,label
S1,"  North  Harbor  "
S2,"North Harbor"
S3,"North Harbor "

Under this cleanup rule, all three labels become North Harbor, with one ordinary U+0020 space and 12 characters. S2 contains an internal U+00A0 space; S3 ends with U+00A0.

Download the sample ↓Open the CSV checker →

Choose what whitespace means for this column

For the display labels in this example, leading and trailing spaces are unwanted and repeated internal spaces should become one. That is a field-specific rule. A fixed-width record, code sample, or identifier may intentionally contain repeated spaces, so do not apply the same cleanup across every column.

Keep label in column B and create cleaned_label in column C. Preserve row_id so each changed value can be traced back. The CSV sample contains real nonbreaking-space characters, which may look like ordinary spaces in your editor. The diagram below names the character explicitly to make the difference visible.

Character or situation	Appropriate treatment
Ordinary edge spaces, U+0020	TRIM removes them.
Repeated ordinary spaces between words	TRIM reduces them to one.
Nonbreaking space, U+00A0	Replace with U+0020 before trimming.
Tabs or line breaks	Decide whether to preserve, replace, or remove.
Meaningful repeated spacing	Keep it; choose a narrower cleanup rule.
Other invisible Unicode characters	Identify the specific character before replacing.

Use an explicit nonbreaking-space formula

In Excel, enter the first formula below in C2 and fill down. In Google Sheets, use the second formula. Both express the same policy for this dataset: change U+00A0 into an ordinary space, then remove unwanted ordinary spacing.

Replace the character with a space rather than an empty string. Deleting the internal nonbreaking space in North Harbor would join the words into NorthHarbor. The objective is to normalize a separator while preserving the words. These formulas use English function names and comma separators; adapt separators to your spreadsheet locale if necessary.

Clean B2 into a helper column

Excel:
=TRIM(SUBSTITUTE(B2,UNICHAR(160)," "))

Google Sheets:
=TRIM(SUBSTITUTE(B2,CHAR(160)," "))

Compare in D2:
=EXACT(B2,C2)

Check cleaned length:
=LEN(C2)

The source has a nonbreaking space between North and Harbor; the normalized label has one ordinary space. Both have length 12. — Illustrated example. Synthetic character diagram: bracketed labels explain the invisible separator and are not literal cell content.

Validate content, not only the length

The first raw label has 17 characters and becomes 12. The second starts at 12 and stays at 12 because one character replaces another. The third changes from 13 to 12 when its trailing nonbreaking space is normalized and trimmed. A length check alone would miss the meaningful change in S2.

Use EXACT to compare original and cleaned strings and inspect the rows that return FALSE. For all three sample rows, FALSE is expected because each contains unwanted spacing. Confirm that every cleaned value is North Harbor and that no row identifiers disappeared.

Inspect invisible characters when TRIM still fails

Excel's TRIM targets ordinary ASCII spaces, and Google documents that its whitespace cleanup does not trim nonbreaking spaces. If the explicit U+00A0 replacement still leaves a mismatch, investigate other characters instead of repeating TRIM.

In Excel, =UNICODE(RIGHT(B4,1)) identifies the trailing character in S3 as decimal 160. In Google Sheets, =CODE(RIGHT(B4,1)) provides the corresponding inspection. Tabs, line breaks, narrow no-break spaces, and zero-width characters are different cases. CLEAN is not a universal Unicode cleanup function; in Excel it targets a limited set of nonprinting characters.

The three raw lengths 17, 12, and 13 become 12, 12, and 12, with S2 requiring a character-level check. — Illustrated example. Synthetic results under the article's rule. S2 changes character identity without changing length, so exact comparison is still needed.

Clean a column or preserve internal spacing in Google Sheets

For many rows, clear C2:C1000, then enter the first formula once in C2. It applies the same cleanup to B2:B1000 while leaving blank source rows visually blank. Keep the remaining output cells free for the formula results. Adjust the bounded range to include your data; do not paste this formula into the source column.

If repeated spaces between words are meaningful, use the second formula in a separate helper cell instead. It normalizes U+00A0 but removes ordinary spaces only at the start or end. For S1, the result is North followed by two spaces and Harbor: 13 characters. The standard TRIM result has 12. This option is for text labels; it is not a numeric conversion.

Google Sheets: two different cleanup rules

Whole-column normalization in C2:
=ARRAYFORMULA(IF(B2:B1000="","",TRIM(SUBSTITUTE(B2:B1000,CHAR(160)," "))))

Edge-only cleanup of B2, preserving internal space runs:
=REGEXREPLACE(SUBSTITUTE(B2,CHAR(160)," "),"^ +| +$","")

Apply the reviewed rule consistently

After reviewing the helper column, copy its results and paste values into a separate cleaned output or the intended destination column. Keep the raw copy and a note describing the replacements. This makes later comparisons easier to explain than an undocumented manual edit.

For lookup keys, clean both datasets with the same agreed rule before comparing them. Then check whether cleanup causes previously distinct keys to collide. Three labels becoming equal may be correct for display text, but it does not prove they identify one real-world entity. The CSV checker can flag selected text risks; it does not run these formulas or decide which whitespace is meaningful.

Common questions

Why does Google Sheets Trim whitespace leave spaces behind?

The Data > Data cleanup > Trim whitespace command does not remove nonbreaking spaces. Replace U+00A0 explicitly with SUBSTITUTE and CHAR(160), then apply the reviewed cleanup formula. If the mismatch remains, inspect the actual character rather than assuming every invisible mark is a space.

How do I remove leading and trailing spaces without changing spaces between words?

Do not use TRIM when repeated internal spaces must remain. The Google Sheets edge-only REGEXREPLACE example removes ordinary edge spaces after normalizing NBSP, while preserving the two spaces inside the first sample label.

Why did the cleaned text change even though LEN stayed the same?

Replacing one nonbreaking space with one ordinary space changes the character without changing the length. That is what happens in S2. Use EXACT or inspect the code point as well as LEN; an unchanged length does not mean the cleanup did nothing.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →