Why does TRIM not fix my VLOOKUP even after I apply it?

You're almost certainly dealing with char 160 (non-breaking space), not char 32. TRIM only removes ASCII spaces. Wrap your lookup value in `=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))` and test again. You can confirm char 160 is present by checking `=CODE(LEFT(A1,1))` or `=CODE(RIGHT(A1,1))` - if either returns 160, that's your culprit.

Does TRIM work on numbers or just text?

TRIM converts its input to text. If you apply `=TRIM(A1)` to a cell containing the number 4200000, the result is the text string "4200000", which will break any formula expecting a number. Wrap with VALUE: `=VALUE(TRIM(A1))`. Or better, fix the source data in place with the Apps Script above, which skips non-string cells entirely.

What's the difference between TRIM and CLEAN in Google Sheets?

TRIM removes leading/trailing spaces and collapses internal runs. CLEAN removes non-printable characters - chars 0-31 in ASCII, which includes line feeds, carriage returns, and tab characters. For ERP exports, you typically need both. CLEAN first, then TRIM: `=TRIM(CLEAN(A1))`.

Can I use ARRAYFORMULA to clean an entire column at once?

Yes. `=ARRAYFORMULA(TRIM(CLEAN(SUBSTITUTE(A2:A1000, CHAR(160), " "))))` placed in a single cell will clean the entire range. For number columns where you also need VALUE, wrap the whole thing: `=ARRAYFORMULA(VALUE(TRIM(CLEAN(SUBSTITUTE(A2:A1000, CHAR(160), " ")))))`. Note that ARRAYFORMULA with VALUE will error on any cell that can't be converted to a number, so use IFERROR around VALUE if the column has mixed content.

How do I do an in-place trim across the whole sheet without formulas?

Use the Apps Script function above - it runs once, replaces all string cells in place, and takes 3-12 seconds depending on sheet size. The alternative native path is Find & Replace (Ctrl+H) with regex enabled: search for `^\s+|\s+$` to catch leading/trailing spaces, replace with nothing. That handles ASCII spaces but won't catch char 160. For a complete clean, Apps Script is the only native option that handles everything in one pass.

Google Sheets Trim Whitespace: FP&A Guide (2026)

This is specifically a finance model problem because the data entering your sheet usually didn't start there. NetSuite GL exports, SAP cost center dumps, HubSpot deal imports - they all carry invisible whitespace that survives a TRIM pass.

Why Whitespace Silently Kills Cross-Tab Lookups

The failure mode is insidious. You've got cost center codes in your P&L tab from an ERP export and the same codes in your Assumptions tab typed by hand. They look identical. The formula returns 0.

=SUMIFS('P&L'!D:D, 'P&L'!C:C, Assumptions!$B$4)

The ERP code is " 6100 " (trailing space). Your Assumptions cell is "6100". Sheets treats them as different strings. No error, no flag, just a silent zero that survives until someone at the board presentation asks why the EBITDA bridge doesn't tie.

Google's own documentation notes that TRIM handles "extra spaces" but explicitly applies only to the standard space character. According to the Google Sheets function documentation, TRIM "removes spaces from text" - and char 160 is not a space in that definition.

The Three-Layer Fix

For data coming out of an ERP or being pasted from a PDF, you need all three in combination.

Layer 1: TRIM for leading/trailing ASCII spaces and internal runs.

Layer 2: CLEAN for non-printable control characters (chars 0-31). Common in Oracle and older SAP exports that embed line-feed characters inside cells.

Layer 3: SUBSTITUTE with CHAR(160) for non-breaking spaces. This is the one TRIM misses and the one most frequently responsible for lookup failures.

The full formula:

=TRIM(CLEAN(SUBSTITUTE('Raw Data'!B2, CHAR(160), " ")))

Apply it across a Lookup Keys tab that your model references instead of the raw import tab directly. That way the raw data stays intact for audit purposes and your model reads from a clean intermediary layer.

For an entire column at once:

=ARRAYFORMULA(TRIM(CLEAN(SUBSTITUTE('ERP Export'!B2:B500, CHAR(160), " "))))

Drop that in your Lookup Keys tab at B2 and it covers the whole range in one cell. As of May 2026, ARRAYFORMULA handles this combination without issue in Google Sheets - no need for a helper column per row.

Numbers Stored as Text After Trimming

Whitespace doesn't just break lookups. When a revenue figure comes in as " 4200000 ", it's a text string. SUMIFS ignores it entirely. After trimming, you still need to force the type conversion:

=VALUE(TRIM(CLEAN(SUBSTITUTE('ERP Export'!C2, CHAR(160), " "))))

Or as an array:

=ARRAYFORMULA(VALUE(TRIM(CLEAN(SUBSTITUTE('ERP Export'!C2:C500, CHAR(160), " ")))))

You can verify the fix worked without inspecting every cell: text-formatted numbers align left, real numbers align right. If your $4.2M revenue column still left-aligns after trimming, you haven't finished.

In-Place Replacement with Apps Script

The formula approach works well when you control the model structure. When someone hands you a messy file and you need to clean it before building on top of it, an in-place replacement is faster.

function trimAllWhitespace() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getUsedRange();
  const values = range.getValues();

  const cleaned = values.map(row =>
    row.map(cell => {
      if (typeof cell !== 'string') return cell; // leave numbers alone
      // Replace non-breaking spaces, then TRIM
      return cell.replace(/\u00A0/g, ' ').replace(/\s+/g, ' ').trim();
    })
  );

  range.setValues(cleaned);
}

Run it once from Extensions → Apps Script → Run. It handles char 160 (that's \u00A0) and collapses all internal whitespace. Skips numeric cells so you don't accidentally stringify your revenue figures.

This processes a 2,000-row sheet in under 3 seconds. For a full GL dump of 10,000+ rows, expect 8-12 seconds before Sheets writes everything back.

Building It Into a Multi-Tab Model

The cleanest pattern for a model that ingests external data: keep a dedicated Data tab for raw imports, a Lookup Keys tab that applies the three-layer clean, and have all your P&L, Cash Flow, and Returns tabs reference Lookup Keys.

='Lookup Keys'!$C$2:$C$500  ← references pre-cleaned entity names

Your P&L SUMIFS formula then reaches into the cleaned column:

=SUMIFS('P&L'!$D:$D,
  'Lookup Keys'!$C:$C, Assumptions!$B$12,
  'P&L'!$A:$A, ">=" & Assumptions!$B$3,
  'P&L'!$A:$A, "<=" & Assumptions!$B$4)

The SUMIFS never touches the raw export directly. If the ERP format changes next quarter and introduces new garbage characters, you fix it in one formula on Lookup Keys and the rest of the model inherits the fix automatically.

This is worth institutionalizing in any model that regularly ingests external data. One bad ERP pull in a bank syndicate DCF or a quarterly board pack creates the kind of error that's embarrassing to explain.

When to Use REGEXREPLACE Instead

For more complex patterns - trailing periods after cost center codes, mixed delimiters, inconsistent capitalization alongside whitespace - REGEXREPLACE is sharper than the SUBSTITUTE/TRIM stack:

=REGEXREPLACE(TRIM(CLEAN(A2)), "\s{2,}", " ")

That handles any Unicode whitespace run greater than one space. The standard TRIM formula only collapses ASCII spaces, so if you're seeing phantom gaps in entity names after a standard TRIM pass, REGEXREPLACE with \s is worth trying.

ModelMonkey for Ongoing Data Ingestion

The manual version of this - export from ERP, paste into Sheets, run cleanup formulas, refresh the model - takes 20-30 minutes per cycle and is exactly the kind of task that introduces transcription errors. ModelMonkey can pull data directly from sources like HubSpot and Stripe into your sheet on a refresh schedule, which means you can wire the three-layer cleanup formula directly onto the ingestion column and never touch the raw export step manually.

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