Excel beginner TRIM CLEAN data cleaning spaces

Excel TRIM & CLEAN: Fix Messy Text Data

TRIM & CLEAN Functions is a Excel function that works from inside out: substitute replaces non-breaking spaces (char 160) with regular spaces. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Data from imports, copies, and web scraping often has invisible junk characters and extra spaces. TRIM and CLEAN fix them.

Generate This Formula Browse Library

The Formula

Prompt

"Clean up text that has extra spaces, line breaks, and non-printing characters from a database import"

Excel 
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Works from inside out: SUBSTITUTE replaces non-breaking spaces (CHAR 160) with regular spaces. CLEAN removes non-printing characters (CHAR 0-31). TRIM removes leading/trailing spaces and collapses internal double-spaces to single spaces.

Step-by-Step Breakdown

  1. CHAR(160) is the non-breaking space — common in web data, invisible but breaks lookups
  2. SUBSTITUTE replaces it with a regular space (CHAR 32)
  3. CLEAN removes all non-printing characters (ASCII 0-31: tabs, line feeds, etc.)
  4. TRIM removes leading/trailing spaces and collapses multiple spaces to one
  5. Nest order matters: SUBSTITUTE first (converts CHAR 160), then CLEAN, then TRIM

Edge Cases & Warnings

  • CLEAN doesn't remove CHAR(160) non-breaking spaces — that's why SUBSTITUTE is needed
  • CLEAN doesn't remove Unicode characters above CHAR(127) — some imported data has these
  • Tabs (CHAR 9) and line breaks (CHAR 10, 13) are removed by CLEAN
  • After cleaning, VLOOKUP/XLOOKUP may start finding matches that previously failed

Examples

Prompt

" Hello World "

Excel 
Hello World
Prompt

"Data\nWith\nLinebreaks"

Excel 
DataWithLinebreaks
Prompt

"Name\xa0Here (non-breaking space)"

Excel 
Name Here

Frequently Asked Questions

Why does VLOOKUP fail even when values look identical?

Usually invisible characters: non-breaking spaces, tabs, or non-printing chars. Apply =TRIM(CLEAN(SUBSTITUTE(cell, CHAR(160), " "))) to both the lookup value and the table.

How do I find cells with hidden characters?

Compare LEN(A1) with LEN(TRIM(CLEAN(A1))). If they differ, the cell has hidden characters.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples