Text

TRIM Function in Excel

Removes extra spaces from text, leaving only single spaces between words.

Syntax

  • =TRIM(text)

Arguments

  • text (required): The text to trim

Examples

  • =TRIM(" Hello World ") - Remove extra spaces - Result: Hello World
  • =TRIM(A1) - Clean cell text - Result: Trimmed text

TRIM best practices

  • Use TRIM before lookup and counting formulas when data comes from exports, CRMs, or copied web tables.
  • Combine TRIM with CLEAN when non-printing characters may be present.

Excel TRIM function documentation

  • TRIM removes leading and trailing spaces and reduces internal runs of spaces to a single space.
  • Syntax: =TRIM(text). Does not remove non-breaking spaces (CHAR(160)) — use SUBSTITUTE for those.
  • Critical before [VLOOKUP](/functions/vlookup/) and [MATCH](/functions/match/) when IDs have invisible spaces.
  • Related: [CLEAN](/functions/clean/), [SUBSTITUTE](/functions/substitute/), [TRIM in VLOOKUP fixes](/problems/vlookup-returning-na/).

What TRIM does and does not fix

  • Removes ordinary space character (CHAR(32)) padding and duplicate spaces between words.
  • Does not remove line breaks from CHAR(10)/CHAR(13) — pair with [CLEAN](/functions/clean/).
  • Does not fix numbers stored as text — use VALUE after TRIM if needed.
  • Non-breaking space CHAR(160): =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).

Step-by-step: clean lookup keys before VLOOKUP

  • Step 1 — IDs in column A may have trailing spaces from CSV import.
  • Step 2 — Helper column: =TRIM(A2).
  • Step 3 — Build VLOOKUP on TRIM keys: =VLOOKUP(TRIM(G2), table, 2, FALSE).
  • Step 4 — Compare =EXACT(A2, G2) vs =EXACT(TRIM(A2), TRIM(G2)) on failing rows.
  • Step 5 — Paste Values on TRIM column if helpers should become static keys.

TRIM vs CLEAN vs TRIM+CLEAN stack

  • TRIM — spaces only.
  • CLEAN — removes first 32 non-printable ASCII characters.
  • Full import cleanup: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
  • Problem: [VLOOKUP returning #N/A](/problems/vlookup-returning-na/) lists space issues.

Worked examples to copy

  • Simple clean: =TRIM(A2).
  • Lookup wrapper: =XLOOKUP(TRIM(E2), TRIM(id_column), return_column).
  • Full name normalize: =TRIM(B2&" "&C2) after trimming parts.
  • Audit extra spaces: =LEN(A2)-LEN(TRIM(A2)) should be 0 when clean.

People also ask

  • Why TRIM not fix VLOOKUP? — Non-breaking spaces, text vs number, or wrong table column.
  • TRIM vs Remove Spaces Power Query? — TRIM in-sheet; Power Query for repeatable ETL.
  • Does TRIM remove tabs? — CLEAN handles many non-printables; tabs may need SUBSTITUTE.
  • TRIM on entire column? — Fill formula down; consider Paste Values for source data cleanup.

Common errors

  • Only removes ASCII space (char 32)
  • Doesn't remove non-breaking spaces

Use cases

  • Data cleaning
  • Import cleanup
  • Text standardization

Frequently asked questions

  • What spaces does TRIM remove? TRIM removes leading spaces, trailing spaces, and reduces multiple spaces between words to single spaces. It only removes ASCII space (character 32), not non-breaking spaces.
  • How do I remove non-breaking spaces? TRIM doesn't remove non-breaking spaces (char 160). Use: =TRIM(SUBSTITUTE(A1, CHAR(160), " ")) to first convert non-breaking spaces to regular spaces, then trim.
  • How do I remove ALL spaces? Use SUBSTITUTE: =SUBSTITUTE(A1, " ", "") removes all spaces. TRIM only normalizes spaces, it doesn't remove them entirely.
  • Why doesn't TRIM work on my imported data? Imported data often contains non-breaking spaces (char 160) or other whitespace characters. Use =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) for thorough cleaning.
  • Can TRIM remove line breaks? No, TRIM only handles spaces. Use CLEAN to remove non-printable characters including some line breaks, or SUBSTITUTE(A1, CHAR(10), "") for line feeds.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use TRIM

  • Data cleaning — common Text scenario for TRIM.
  • Import cleanup — common Text scenario for TRIM.
  • Text standardization — common Text scenario for TRIM.

TRIM in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • TRIM syntax: =TRIM(text)
  • text (required): The text to trim
  • Confirm TRIM arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm TRIM arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • CLEAN (/functions/clean/): Removes all non-printable characters from text.
  • SUBSTITUTE (/functions/substitute/): Replaces occurrences of old text with new text in a string.
  • LEN (/functions/len/): Returns the number of characters in a text string.

Comparisons involving this function

  • CONCAT vs TEXTJOIN (/compare/concat-vs-textjoin/): Use CONCAT for simple joining without a delimiter. Use TEXTJOIN when you need separators, optional blank skipping, and cleaner multi-cell joins.

Common problems and fixes

  • Why Is VLOOKUP Returning #N/A? (/problems/vlookup-returning-na/): Use FALSE for exact match, confirm the lookup value exists in the first column, and clean hidden spaces or mismatched data types.
  • Why Is XLOOKUP Not Finding a Value? (/problems/xlookup-not-finding-value/): Check for extra spaces, mismatched text/number types, wrong lookup_array, and match_mode settings before adding if_not_found text.
  • Why Is COUNTIF Not Counting Text? (/problems/countif-not-counting-text/): Clean the source data with TRIM and CLEAN, then use exact text criteria or wildcards such as "*text*".

Errors to watch for

  • Only removes ASCII space (char 32) — review causes on linked error pages in the directory.
  • Doesn't remove non-breaking spaces — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter TRIM on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

TRIM worked examples to copy

  • =TRIM(" Hello World ") — Remove extra spaces. Expected result: Hello World.
  • =TRIM(A1) — Clean cell text. Expected result: Trimmed text.

TRIM reference summary for crawlers and offline review

  • TRIM belongs to the Text category in Excel. Removes extra spaces from text, leaving only single spaces between words.
  • Full syntax: =TRIM(text). Open /functions/trim/ for parameters, FAQs, and related pages.
  • Common mistakes: Only removes ASCII space (char 32); Doesn't remove non-breaking spaces
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.