Excel TRIM Function

TRIM removes extra spaces from a piece of text, leaving exactly one space between words and none at the start or end. It's the function you reach for when data pasted from a website, a PDF, or another system looks fine but won't match, sort, or sum correctly. The catch: TRIM only removes the standard space character. Text copied from the web often contains a different, invisible character that looks like a space but isn't one, and TRIM walks right past it.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and earlier versions. TRIM has been part of Excel since the earliest releases and works identically across every current version.

Syntax

=TRIM(text)

Parameter	Required	Description
`text`	Yes	The cell reference or text string you want cleaned. Can be typed directly in quotes or point to a cell.

ℹ

A "leading space" is a blank space before the first visible character in a cell, and a "trailing space" is one after the last visible character. Both are invisible until you click into the cell.

Basic Example

You've imported a customer list where names were copied from a web form, and some entries have extra spaces scattered through them.

=TRIM(A2)

// A2 = the cell containing "  Maria   Gonzalez "

TRIM scans the text in A2, collapses the run of spaces between "Maria" and "Gonzalez" down to one, and strips the leading and trailing spaces entirely. The result is a clean "Maria Gonzalez". Nothing else about the text changes: capitalization, punctuation, and word order stay exactly as they were.

How TRIM Works

It only removes the ASCII space character

TRIM was built to remove one specific character: the regular space, which has the ASCII value 32. That's it. It doesn't know about tabs, line breaks, or any of the dozen other characters that render as blank space on screen. This narrow definition is the root cause of almost every "TRIM isn't working" problem.

Multiple spaces between words collapse to one

If a cell contains "Q1 Report" with five spaces between the words, TRIM returns "Q1 Report" with a single space. It doesn't matter how many spaces are there originally. Two, ten, thirty: TRIM reduces any run of spaces down to exactly one.

Leading and trailing spaces disappear completely

Unlike the space between words, spaces before the first character or after the last one are removed entirely, not reduced to one. So " Total " becomes "Total" with nothing on either side.

It returns text, not a formatted value

TRIM always outputs a text string, even if the original cell contained something that looked like a number. This matters more than it sounds like it should, and it's covered in detail below.

Common Use Cases

Cleaning lookup keys before matching

Extra spaces are one of the most common reasons a lookup formula fails to find a value that's clearly sitting right there in the table.

=XLOOKUP(TRIM(A2), customer_ids, customer_names)

// TRIM strips hidden spaces from A2 before XLOOKUP tries to match it

Combining TRIM with CLEAN for imported data

Data pulled from PDFs or external systems often carries both extra spaces and non-printing control characters like stray line feeds. TRIM handles the spaces; CLEAN handles most of the control characters.

=TRIM(CLEAN(A2))

// CLEAN removes non-printing control characters first
// TRIM then collapses any resulting extra spaces

Order matters here. Run CLEAN first, then TRIM, so any spaces left behind after CLEAN strips out control characters get cleaned up too.

⚠

CLEAN also strips line breaks. If a cell intentionally contains multi-line text, such as an address split across two lines, wrapping it in CLEAN will flatten it into one line. Skip CLEAN for any column where line breaks carry meaning.

Counting words without TEXTSPLIT (works in any Excel version)

Newer Excel versions can count words with TEXTSPLIT, but that function isn't available before Excel 365. This version works everywhere, including Excel 2016 and 2019.

=LEN(TRIM(B2)) - LEN(SUBSTITUTE(TRIM(B2), " ", "")) + 1

// TRIM removes stray leading, trailing, and doubled spaces first
// The LEN difference counts how many spaces remain between words
// Adding 1 converts space count to word count

TRIM does the setup work here: without it, extra spaces would inflate the count and give you the wrong number of words.

Bulk-cleaning an entire imported table

For one-off cleanups inside a worksheet, wrapping every cell in TRIM works fine. For recurring imports (a CSV you pull in every week, for example), doing the cleanup in Power Query instead of formulas means the fix happens automatically every time the data refreshes, with no formulas to drag down or maintain.

Handling Errors

TRIM doesn't generate its own errors under normal use; it accepts almost anything and returns text. The one place it throws an error is when it's pointed at a cell that already contains an error, such as #N/A or #REF!. In that case, TRIM simply passes the existing error through.

Common causes of an error showing up through TRIM:

The referenced cell already contains an error from another formula
The reference itself is broken, for example after deleting a row or column
TRIM is nested inside a formula (like VLOOKUP or XLOOKUP) that's the actual source of the error

=IFERROR(TRIM(A2), "")

If TRIM returns an error, check the source cell first. TRIM almost never causes the error itself; it's usually just reporting a problem that already existed upstream.

Notes & Gotchas

How do I use the TRIM function in Excel?

Type =TRIM( followed by the cell reference you want to clean, then close the parenthesis. For example, =TRIM(A2) cleans the text in cell A2 and returns the result in the cell where you entered the formula. To apply it to a whole column, enter it in the first row and drag the fill handle down, or convert your range to an Excel Table so the formula fills automatically.

What is the difference between TRIM and CLEAN in Excel?

TRIM removes extra space characters; CLEAN removes non-printing control characters like line breaks, tabs, and other characters generated by software but not typed by a person. They solve different problems and are often used together, with CLEAN wrapped inside TRIM: =TRIM(CLEAN(A2)). Neither function removes a non-breaking space, which is the character that trips up most people.

Why is TRIM not removing spaces in Excel?

The gap almost always sits in a character that only looks like a space. The most common offender is the non-breaking space (character code 160), which shows up constantly in text copied from web pages, but plenty of other invisible characters can cause the same problem, including narrow no-break spaces and various Unicode whitespace characters used in text copied from PDFs or non-English sources.

To find out exactly what you're dealing with, use =CODE(MID(A2,1,1)) on the first character of a stubborn cell, and repeat it across a few positions if the problem is buried mid-string. Once you know the character code, replace it with a real space and then run TRIM:

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

// CHAR(160) = the non-breaking space, converted to a regular space
// TRIM then removes any extra spaces the substitution creates

If CODE returns something other than 160, swap in that value instead. This CODE-and-SUBSTITUTE combination works for any invisible character, not just the common one, which is the fastest way to diagnose a "TRIM isn't working" problem without guessing.

How do you remove spaces in Excel without TRIM?

Find & Replace (Ctrl+H) can remove spaces if you know exactly which character to target, though it's easy to accidentally remove spaces you wanted to keep. SUBSTITUTE gives you more control for a single known character: =SUBSTITUTE(A2, " ", "") removes every space, not just extras. For recurring cleanup on imported files, Power Query's Trim and Clean transforms handle the same job as a repeatable step, without formulas cluttering the sheet.

Does TRIM function work on numbers in Excel?

TRIM works on numbers only in the sense that it converts them to text first. If A2 contains the number 100 with trailing spaces, =TRIM(A2) returns "100", but that result is text, not a number. It will look identical to a real number but sit left-aligned instead of right-aligned, and it won't sum correctly in a SUM formula until you convert it back with something like =VALUE(TRIM(A2)).

✓

If a trimmed column of numbers won't total correctly, check the alignment. Text defaults to left-aligned and numbers default to right-aligned; that visual mismatch is usually the first clue that TRIM converted your numbers to text.

What happens if the cell is already empty?

TRIM returns an empty text string (""), not an error. This is safe to use in comparisons and concatenation, but note that an empty string is not the same as a truly blank cell for formulas that specifically test with ISBLANK.

Related Functions

Function	Use this when...
`TEXTSPLIT`	You're on Excel 365 and want to split or count words directly instead of using SUBSTITUTE and LEN.