Why does TRIM not remove spaces from my string in Google Sheets?

If `TRIM` isn't working, the "spaces" are almost certainly non-breaking spaces (CHAR(160)) from a PDF or web copy-paste, not regular spaces (CHAR(32)). TRIM only strips ASCII 32. Use `=TRIM(SUBSTITUTE(A1, CHAR(160), " "))` instead. You can confirm the issue by checking `=CODE(MID(A1,1,1))` - if it returns 160 instead of 32, that's your culprit.

How do I remove all spaces from a string in Google Sheets, including spaces between words?

Use `=SUBSTITUTE(A1," ","")` to remove every regular space entirely. For a ticker like `"BRK B"` becoming `"BRKB"`, this is the right approach. If non-breaking spaces are also present, chain it: `=SUBSTITUTE(SUBSTITUTE(A1, CHAR(160), ""), " ", "")`. Note that TRIM only collapses extra spaces - it won't remove the single space between words.

How do I clean spaces in a cross-tab VLOOKUP that keeps returning #N/A?

Wrap both the lookup value and lookup array in TRIM: `=VLOOKUP(TRIM(A2), ARRAYFORMULA(TRIM('Reference'!$A$2:$B$500)), 2, 0)`. This handles mismatches where either the lookup value or the source data has trailing/leading spaces. For INDEX/MATCH, apply TRIM to both the match value and match array the same way.

What's the difference between TRIM, CLEAN, and SUBSTITUTE for removing spaces?

TRIM removes leading/trailing spaces and collapses multiple internal spaces to one. CLEAN removes non-printable control characters (ASCII 0-31) but not spaces. SUBSTITUTE replaces any specific character you target - including spaces and CHAR(160). For data coming from external sources, combine all three: `=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))`.

Does ARRAYFORMULA work with TRIM across an entire column?

Yes. `=ARRAYFORMULA(TRIM(A2:A))` processes the entire column in one formula. Wrap it in `IF(LEN(A2:A)>0, TRIM(A2:A), "")` to avoid returning empty strings for blank cells, which can cause issues with COUNTA and downstream filters. For large ranges (1,000+ rows), this is significantly faster than a drag-down approach and updates automatically when new rows are added.

How to Remove Spaces from Strings in Google Sheets (2026)

4 Ways to Remove Spaces from Strings in Google Sheets

Here's how the options stack up:

Function	Removes	Misses	Best for
`TRIM(A1)`	Leading, trailing, extra internal spaces	CHAR(160), control characters	Paste from Word, manual entry
`SUBSTITUTE(A1," ","")`	All regular spaces	Non-breaking spaces, tabs	Ticker/code cleanup
`CLEAN(A1)`	Non-printable ASCII 0-31 range	Spaces of any kind	Bad exports from legacy ERP
`SUBSTITUTE(TRIM(A1), CHAR(160)," ")`	CHAR(160) non-breaking spaces	Control characters below 32	PDF/web copy-paste

For most finance data cleanup - pasting account names from a general ledger export or reconciling a vendor list - TRIM alone gets the job done. Start there.

How to Remove Spaces from Strings with TRIM

TRIM strips leading and trailing spaces, and collapses any run of internal spaces down to a single space. The syntax is simple:

=TRIM(A2)

In a 3-statement model where account names pulled from NetSuite have trailing spaces, your SUMIFS breaks silently. The lookup finds zero matches because "Revenue " and "Revenue" don't match. TRIM fixes that.

For a full column on your P&L tab:

=ARRAYFORMULA(IF(LEN('Raw Data'!B2:B)>0, TRIM('Raw Data'!B2:B), ""))

This processes 500+ rows in a single formula pass, no drag-down required. The IF(LEN(...)) wrapper stops TRIM from padding blanks with empty strings that break downstream COUNTIFs.

Cross-tab version that cleans account names while pulling actuals:

=SUMIFS(
  'P&L'!D:D,
  ARRAYFORMULA(TRIM('P&L'!B:B)), ">="&Assumptions!$B$3,
  ARRAYFORMULA(TRIM('P&L'!A:A)), "Revenue"
)

Remove Spaces from Strings Across 500+ Rows: The Non-Breaking Space Problem

If you've ever copied financial data from a PDF, a web portal, or a vendor invoice into Sheets and TRIM did nothing, the spaces probably aren't spaces. They're CHAR(160) - the non-breaking space character, ASCII code 160 instead of 32.

TRIM doesn't touch CHAR(160). Neither does SUBSTITUTE(A1," ",""). They're different characters entirely.

The fix:

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))

This substitutes every non-breaking space with a real space first, then TRIM cleans up the result. As of mid-2026, this is still the most reliable single formula for copy-pasted web data in Google Sheets.

For a column of 800 company names pulled from a Bloomberg export into your comp table:

=ARRAYFORMULA(
  IF(LEN('Comps'!C2:C)>0,
    TRIM(SUBSTITUTE('Comps'!C2:C, CHAR(160), " ")),
  "")
)

When CLEAN Actually Matters

CLEAN targets non-printable control characters - the ASCII 0-31 range - that show up in exports from older ERP systems, mainframe outputs, and some bank CSV feeds. These characters are invisible and don't affect cell display, but they silently break TEXT functions and VLOOKUP matches.

=CLEAN(TRIM(A2))

Run both together. CLEAN handles the control characters, TRIM handles the spaces. The order matters: CLEAN first can turn some control characters into spaces, which TRIM then catches.

A full defensive formula for dirty GL exports going into a $2.4M revenue reconciliation:

=ARRAYFORMULA(
  IF(LEN('GL Export'!A2:A)>0,
    TRIM(CLEAN(SUBSTITUTE('GL Export'!A2:A, CHAR(160), " "))),
  "")
)

This is the nuclear option. Overkill for clean internal data. The right call for anything coming from a third-party feed you don't control.

Cleaning Data for VLOOKUP and INDEX/MATCH

The most common reason to care about this isn't aesthetics. It's that your lookups return #N/A when the underlying data has invisible whitespace.

If your Returns Analysis tab is pulling entity names from a Assumptions tab, and one of them came from a PDF pitch deck via copy-paste, you get a silent mismatch. No error message. Just a zero where the WACC should be.

Wrapping the lookup range in TRIM is faster than cleaning the source data manually:

=INDEX(
  Assumptions!$C$2:$C$50,
  MATCH(
    TRIM(B12),
    ARRAYFORMULA(TRIM(Assumptions!$B$2:$B$50)),
    0
  )
)

This matches TRIM(B12) against a TRIM'd lookup array, so it works regardless of which side has the extra spaces. The performance cost on 50 rows is negligible. On 5,000 rows it slows slightly - at that point, clean the source once and reference the cleaned version.

Automating Data Cleanup with ModelMonkey

If you're running this cleanup on a fresh export every week - a new GL pull, a refreshed comp table, updated runway data - doing it manually is one of those tasks that looks like 10 minutes but costs an hour once you add the checking and the fixing.

ModelMonkey can handle this kind of structural cleanup in the sidebar: describe what you're working with, and it writes and applies the ARRAYFORMULA across the right range without you specifying each column. Useful when you're onboarding a new data feed and don't know yet which columns have the CHAR(160) problem and which don't.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.