Knowledge base / Excel

TRIM in Excel: ordinary and nonbreaking spaces

I show which spaces TRIM handles and how to replace a nonbreaking space without joining words or overwriting the source.

Kajo RudzińskiPublished Updated 4 min read

A mechanical comb aligns the spacing between golden blocks

Two product names look identical, but a lookup finds only one. Before rebuilding the formula, I inspect the spacing. A trailing space is an unimpressive but effective way to break a report.

TRIM handles ordinary spaces. It does not remove every invisible character. I will show its boundary, replace a nonbreaking space without joining words, and keep the cleanup visible in a helper column.

Syntax and ordinary spaces

=TRIM(A2)

TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words to one. Microsoft identifies the ASCII space, code 32, rather than every Unicode spacing character.

These examples are synthetic. Quotes in the table only mark text boundaries; do not enter them as part of the cell contents.

Text in A2 Result in B2
" Ground coffee " "Ground coffee"
"Tea" "Tea"
"A B" "A B"

The final example is also a warning. If two spaces are intentional in a product code, TRIM changes its meaning. Establish whether spacing is an error or part of an identifier before cleaning it.

I use English names and commas. The Polish function is USUŃ.ZBĘDNE.ODSTĘPY; separators depend on regional settings. The examples use UNICHAR, which Microsoft’s documentation lists for Microsoft 365 and Excel releases from 2016 through 2024.

Replace a nonbreaking space with an ordinary one

A nonbreaking space has Unicode code point 160 and often arrives in text copied from websites. TRIM alone leaves it in place. To make the example reproducible without hiding an invisible character in the article, create this synthetic text in A2:

="Ground"&UNICHAR(160)&"coffee"

In B2 enter:

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

SUBSTITUTE first changes the nonbreaking space into an ordinary space. TRIM then normalizes the ordinary spacing. The result is Ground coffee, with one ordinary space separating the words.

The third SUBSTITUTE argument contains one space: " ". Replacing it with the empty string "" would produce Groundcoffee. That is a different transformation and usually the wrong choice for a product name.

UNICHAR creates a character from its Unicode number. SUBSTITUTE replaces specified text. You may see older examples using CHAR; its Polish name is ZNAK. I use an explicit Unicode code point here because CHAR depends on the environment’s character set, as Microsoft documents.

A wide spacer between cargo groups is replaced by a smaller gap

Tabs and line breaks need a separate decision

CLEAN removes the first 32 nonprinting ASCII characters. It does not cover every invisible Unicode character either. The scope is described in Microsoft’s CLEAN reference.

Removing a line break between two words can join them. When a separator carries meaning, I first replace the identified character with an ordinary space and then normalize spacing. I do not add CLEAN to every formula simply because its name sounds reassuring.

If text still fails to match, investigate the specific character and its source. Other invisible characters may require other replacements. A correct formula for code point 160 does not guarantee that an entire import is clean.

Text cleanup and numerical conversion

TRIM returns text. I do not use it as a numerical conversion tool. For a text value such as " 120 ", I can remove surrounding spaces first and then deliberately convert the result. Microsoft explains converting numbers stored as text.

An already numerical input does not need text-space removal. Avoid using TEXT with an empty format as a supposed repair mechanism. And if 00120 is an identifier, converting it to a number may remove meaningful leading zeros.

The cleanup rule should follow the field’s meaning. A name, a numerical amount and a fixed-format product code can look similar in a spreadsheet but require different treatment. I keep those decisions explicit instead of applying the same transformation to every column.

Keep the change reviewable

Leave the source in column A and place the formula in B. Fill down, then compare ordinary text, repeated spaces, a nonbreaking space and a code that must stay unchanged. Decide whether to replace the source with values only after inspecting the result.

A macro that overwrites the selection is unnecessary for this small task. A helper column retains the source and makes the transformation visible. For recurring imports, consider a broader data cleaning process.

Finally, inspect matching in the report, for example with SUMIFS. Cleaning should help select the correct records without accidentally merging different products under the same altered code.

Key takeaways

Does TRIM remove nonbreaking spaces?

Not by itself. Replace Unicode character 160 with an ordinary space using SUBSTITUTE, then apply TRIM.

Should I replace a nonbreaking space with an empty string?

For names, an ordinary space usually preserves word separation. An empty string removes the separator and can join words.

Is the result a number?

TRIM returns text. Converting an amount to a number is a separate step; identifiers may need to remain text.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.