Two words become one
CLEAN removes control characters. Replace separators with spaces first if they should keep words apart.
Clean spaces and line breaks in imported text
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Clean A2:A7 into B2:B7. Replace nonbreaking spaces, tabs and line breaks with spaces, remove other ASCII control characters, and collapse ordinary spaces.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: B2, B3, B4, B5, B6, B7. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Turn messy imported labels into readable text while making the cleaning rules explicit.
THE QUICK ANSWER01
SUBSTITUTE turns the listed separators into ordinary spaces. CLEAN removes remaining ASCII controls. TRIM then removes excess ordinary spaces at the ends and between words.
ƒx
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(10)," "),CHAR(13)," "),CHAR(9)," ")))For the example data
B2 Maya ReedB3 Noor PatelB4 West OfficeB5 Blue TeamB6 East DeskB7
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Cleaning is a choice about meaning
This rule is appropriate for short labels where tabs and line breaks should act like word separators. A postal address or a multiline note may need to keep its line breaks. Decide what the output should preserve before applying a batch change.
TRIM does not fix every space
The formula explicitly replaces character 160. Other Unicode spaces, such as an em space, remain unchanged. The edge-case data includes one so the limitation is visible rather than hidden.
Keep the original column
Write the clean result beside the imported text. Compare the two before replacing source data. This formula preserves letter case and does not change numbers stored as text into numeric values.
Read the full step-by-step walkthrough
- Inspect the unwanted characters
An imported label may contain repeated spaces, nonbreaking spaces, tabs or line breaks. They need different treatment.
- Replace the nonbreaking space
CHAR(160) identifies the common nonbreaking space. Replace it with an ordinary space so TRIM can handle it.
SUBSTITUTE(A2,CHAR(160)," ") - Keep words apart at line breaks
Replace line-feed, carriage-return and tab characters with spaces before cleaning. Simply deleting them could join two words.
CHAR(10), CHAR(13), CHAR(9) - Remove remaining control characters
CLEAN removes remaining characters in the basic ASCII control range. It does not remove every invisible Unicode character.
CLEAN(…) - Normalize ordinary spaces
TRIM removes leading and trailing ordinary spaces and reduces repeated ones between words to a single space. Fill the formula down.
TRIM(…)
Things to watch
A strange space survives
TRIM primarily handles ordinary spaces. An unlisted Unicode space is outside this formula’s rule.
Text becomes a different case
This cleaning task preserves case; do not add LOWER or UPPER unless you intend to change it.
About this example
Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.
Content version 2. Practice checks compare calculated results across different datasets; they do not certify every possible formula.