The formula
Excel & Google SheetsExcelGoogle Sheets
=TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," ")))In C2 → Blue ceramic mug
Length with TRIM alone (non-breaking spaces stay)ExcelGoogle Sheets
=LEN(TRIM(A2))In D2 → 17
Line breaks to spacesExcelGoogle Sheets
=TRIM(SUBSTITUTE(SUBSTITUTE(A3,CHAR(10)," "),CHAR(13)," "))In C3 → Line one Line two
CLEAN alone joins the linesExcelGoogle Sheets
=CLEAN(A3)In D3 → Line oneLine two
✓ Tested. All 4 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Raw text | |||
| 2 | Blue ceramic mug | Blue ceramic mug | 17 | |
| 3 | Line one Line two | Line one Line two | Line oneLine two |
How it works
- SUBSTITUTE(A2,UNICHAR(160)," ")
- Turns non-breaking spaces (Unicode 160) into normal spaces. CHAR(160) also works in Windows Excel and Google Sheets, but Excel for Mac's CHAR uses the Mac character set, so UNICHAR (Excel 2013+) is the safer choice.
- CLEAN(…)
- Removes non-printing characters (codes 0–31), including line breaks.
- TRIM(…)
- Removes leading/trailing spaces and collapses runs of spaces.
Common errors and fixes
Line break disappears but words join?
CLEAN deletes the break without adding a space. Use SUBSTITUTE(A2,CHAR(10)," ") first if you want a space.
Still not matching?
Other invisible characters such as zero-width spaces (UNICHAR(8203)). SUBSTITUTE those too.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.