Module 03: Build dependable worksheets
Clean hidden spaces without losing the original text
Inspect ordinary and nonbreaking spaces, apply a named cleanup rule, and compare the output with its source.
Excel 2016, 2019, 2021, 2024, and Microsoft 365 for Windows desktop. Uses TRIM, SUBSTITUTE, UNICHAR, UNICODE, MID, and LEN. English formulas use commas; some locales require semicolons. Time is an estimate.
Useful first: Build your first formulas from cell references · Copy formulas while keeping shared inputs fixed
- Identify a nonbreaking space instead of guessing from appearance.
- Create a helper-column cleanup that preserves source cells.
- Check both characters and meaning before replacing original values.
Small data. A result you can check.
Synthetic Unicode text. S2 contains an internal U+00A0; S3 ends with U+00A0. Do not trim the sample before the exercise.
View the raw practice data
RowID RawLabel S1 North Harbor S2 North Harbor S3 North Harbor
01Create three labels that look almost identical
Paste the sample into A1:B4. B2 has two ordinary spaces at each edge and two between North and Harbor. B3 has a nonbreaking space between the words. B4 has one ordinary internal space and a trailing nonbreaking space. The row IDs let you trace each output back to its source.
If your copy-and-paste path normalized those characters, recreate B3 with ="North"&UNICHAR(160)&"Harbor" and B4 with ="North Harbor"&UNICHAR(160). Those setup formulas deliberately construct the invisible characters; they are not part of the downloadable data.
| Cell | Original length | Important character |
|---|---|---|
| B2 | 17 | Ordinary spaces at edges and internally |
| B3 | 12 | U+00A0 at position 6 |
| B4 | 13 | U+00A0 at the end |
02Inspect the character before deciding what to replace
Enter =UNICODE(MID(B3,6,1)) in F2. It returns 160, identifying the space between the words. For an ordinary space the result would be 32. Two strings can therefore have the same length and appearance while containing different characters.
TRIM alone handles ordinary spaces, including repeated spaces inside text, but does not remove the nonbreaking space used here. CLEAN has a different role: it removes a limited set of nonprinting characters. It is not a universal Unicode cleanup, and deleting a line break can join words that should remain separated.
03Normalize in a helper column
Enter CleanLabel in C1 and use the formula below in C2, then fill through C4. SUBSTITUTE first changes U+00A0 to an ordinary space. TRIM then removes edge spaces and reduces internal runs of ordinary spaces to one. The original B column stays available for comparison.
Enter Length in D1 and =LEN(C2) in D2, filling down. All outputs should be North Harbor with length 12. This rule intentionally changes B2's internal gap. If repeated internal spacing is meaningful, use an edge-only workflow instead of treating this formula as a safe default for every column.
=TRIM(SUBSTITUTE(B2,UNICHAR(160)," "))Enter in C2 and fill through C4. Keep source cells B2:B4 until the cleanup has been reviewed.
Prove that the clean labels match character-for-character, then decide whether the cleanup is suitable.
- In E1 enter MatchesExpected; in E2 enter =EXACT(C2,"North Harbor") and fill through E4.
- Compare the original and helper columns row by row, including B2's intentional reduction from two internal spaces to one.
- Copy C2:C4 to a separate destination using Paste Special > Values only after approving that rule. Retain the source sheet and row IDs.
Show the worked answer
C2:C4 are all North Harbor; D2:D4 are 12; E2:E4 are TRUE.
F2 is 160. The original lengths 17, 12, and 13 demonstrate that a length check alone cannot identify every hidden-character difference.
Check your work- The output contains one ordinary internal space, not U+00A0.
- No row was deleted, sorted, or merged.
- The chosen cleanup rule is recorded before using the same transformation on other fields.
One question before you move on.
Ready for the next step?
Mark this lesson when you can explain the idea and reproduce the practice result.
Your checklist stays on this browser.Reference notes
These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.
Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.