How to Remove Leading, Trailing, and Extra Spaces in Excel

Hidden spaces are the #1 cause of broken VLOOKUP, XLOOKUP, and COUNTIF formulas. If cell A2 has "Apple " and your lookup is searching for "Apple", Excel will return #N/A.

---

1. The Standard TRIM Formula

=TRIM(A2)

• Removes all leading spaces at the start of the string.
• Removes all trailing spaces at the end of the string.
• Condenses internal double spaces down to single spaces.

---

2. The Ultimate Bulletproof Clean Formula (Web Imports)

When data is copied from web pages or ERP systems, it often contains Non-Breaking Spaces (CHAR(160)) and Non-Printable Line Breaks (CHAR(1) to CHAR(31)):

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

Breakdown of the 3-Layer Cleanser:

1. SUBSTITUTE(A2, CHAR(160), " "): Converts invisible non-breaking spaces into normal standard spaces.
2. CLEAN(...): Removes invisible ASCII control characters (carriage returns, tabs, null bytes).
3. TRIM(...): Strips out the remaining leading, trailing, and excessive spaces.