Clean unwanted spaces without changing codes

Distinguish edge spaces, repeated internal spaces and nonbreaking spaces.

Two names can look identical and still fail a lookup because one contains an invisible trailing space. But removing every space can damage a product code or a deliberately spaced label. Decide which characters may change before choosing a cleaning formula.

Decide whether repeated spaces are meaningful

This exercise normalizes display labels. It is not an instruction to trim every identifier, address or free-text note in a delivery. Write down which columns may change and retain the raw column beside the cleaned result. A leading or trailing ordinary space can be hard to see on a webpage; use the downloadable values and the length checks below rather than retyping what appears on screen. A non-breaking space is a different character, even when it looks identical.

Set up the practice sheet

Download spaces-input.csv and spaces-expected.csv. Use the input file for the exercise; the expected file is a comparison, not another input to append.

The input has three columns: row_id in A, raw_text in B and case in C, with headers in row 1 and five records in rows 2:6. Use a UTF-8, comma-delimited import with raw_text as Text. If this is your first import, follow the text import settings and then return here. Before applying formulas, check the raw lengths below so an earlier import or paste has not already removed spaces.

Use =LEN(B2) in a spare cell to inspect the first input. If it is already 3, stop and reload the untouched sample. This guide's native formula check used entered worksheet strings; it does not certify every CSV import route.

Identify the rule for this column

The sample has five cases. S01 contains two ordinary spaces before and after Ana. S02 contains two spaces between North and Office. S03 has a nonbreaking space on either side of Bo. S04 is the code ACME 07, with two internal spaces that may be meaningful. S05 is already clean.

For ordinary display labels, this exercise permits replacing nonbreaking spaces with ordinary spaces, trimming edges and collapsing repeated internal spaces. For identifiers, the alternative rule trims only ordinary edge spaces after that replacement and keeps internal spacing. Neither rule should be applied automatically to all columns.

Create a helper column

Import the input as text, retain raw_text, and put the proposed cleaned label in D2, under a normalized_label header in D1. If raw_text is in B2, use:

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

Fill D2 down through D6, the fifth data record. Put the change-flag formula below in E2 and fill it down through E6. The formula replaces U+00A0 with an ordinary space, then normalizes ordinary spaces. In Excel installations that use semicolons between function arguments, adapt the formula separators. A comma inside a quoted string would still be part of the string.

Before replacing the source, add a change flag:

=NOT(EXACT(B2,D2))

Here D2 is the helper result. Inspect the flagged rows, especially identifiers. Do not treat a TRUE flag as proof that the cleaned value is correct; it only tells you that something changed.

Compare two valid outcomes

The table preserves repeated spaces; the downloadable expected CSV also lets you inspect exact characters. For S04, normalized_label has seven characters and edge_trim_only has eight. For S03, raw_text has four characters and the cleaned value has two.

The TRIM formula produces the normalized_label column, not the edge-only column. If internal spacing matters, preserve that source field or use an explicitly configured edge-trimming transformation. Do not apply this formula to a code column merely because it worked for names.

Keep the change reversible

Keep the raw and helper columns until lookups, counts and spot checks agree. If two previously different keys become the same after normalization, investigate before merging records. Once reviewed, copy the helper values into a new output table; keep the original workbook as the audit copy.

TRIM does not remove every Unicode whitespace character. A remaining mismatch may involve a tab, narrow nonbreaking space or another character. Diagnose it instead of repeatedly adding global replacements.

Reference

Microsoft documents ordinary-space behavior and the nonbreaking-space exception in TRIM.

Verification scope

On September 30, 2026, Excel for Mac 16.112.2 calculated TRIM, SUBSTITUTE and UNICHAR on the five sample strings entered into a worksheet. The normalized results were Ana, North Office, Bo, ACME 07 and Chen; all five EXACT comparisons returned TRUE. Raw lengths were 7, 13, 4, 8 and 4; output lengths were 3, 12, 2, 7 and 4. This validates the displayed normalization formula, not an edge-only formula or every CSV import route. Expected edge-only strings also passed independent file-level checks.