Excel’s TRIM function removes extra spaces from text while preserving single spaces between words.
Learn how to use it in a cell range, and how to handle invisible characters that can keep two values from matching.
What formula removes extra spaces in Excel?
For ordinary spaces, use =TRIM(A2).
If A2 contains Avery Lee , the formula returns Avery Lee: it removes spaces at the beginning and end and reduces repeated spaces between words to one.
TRIM changes the spacing in its result; it does not edit the original cell.
Put the formula in another column, fill it down, then copy the cleaned results and use Paste Special > Values if you want to replace the original text.
How to remove extra spaces from a column
- Suppose the source data is in A2:A100.
Enter=TRIM(A2)in B2. - Fill the formula down through B100 using the fill handle or by copying and pasting.
- Compare the cleaned values with the source.
If they look correct, copy B2:B100. - To replace the source text, select A2:A100 and choose Paste Special > Values.
Keep a backup if the original spacing may matter.
For example, if A2 contains green tea , B2 returns green tea.
If A3 contains green tea already, the result remains green tea.
Remove every space, including spaces between words
TRIM preserves one space between words.
If you need to remove all ordinary spaces, use SUBSTITUTE instead:
=SUBSTITUTE(A2," ","")
For example, green tea becomes greentea.
This is useful for normalizing product codes or identifiers when spaces are not meaningful.
It is usually unsuitable for names or sentences because it joins the words.
To remove repeated spaces but keep single spaces, use TRIM.
To remove spaces only from the start and end while preserving repeated spaces inside, use a different approach; TRIM does not make that distinction.
Why can TRIM leave spaces behind?
Excel’s TRIM targets the standard space character (ASCII 32).
Text pasted from websites, reports, or other applications can contain nonbreaking spaces (character 160) or other nonprinting characters.
They may look like spaces but remain after TRIM, so lookups, comparisons, or duplicate checks can still fail.
For a common nonbreaking-space issue, replace character 160 with a regular space before trimming:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
For example, if a copied value contains a nonbreaking space between North and Shore, this formula converts it to an ordinary space and then normalizes spacing, returning North Shore.
If the text includes line breaks or other control characters, try CLEAN as well:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
CLEAN removes many nonprinting ASCII control characters, while SUBSTITUTE handles the common nonbreaking space.
CLEAN does not remove every possible Unicode character, so unusual imported text may need additional character-specific substitutions.
Which formula should you use?
- Remove leading and trailing spaces and collapse repeats:
=TRIM(A2) - Remove every ordinary space:
=SUBSTITUTE(A2," ","") - Handle nonbreaking spaces and repeated spaces:
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")) - Clean common control characters too:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
Choose based on the data’s purpose.
A person’s name generally needs single spaces between words; an identifier may need no spaces at all.
Check a few source and result pairs before applying a cleanup to a large range.
Excel version compatibility
TRIM, SUBSTITUTE, CHAR, and CLEAN are longstanding worksheet functions available in common desktop versions of Excel, including Excel 2016, Excel 2019, Excel 2021, Excel 2024, and Microsoft 365, as well as Excel for the web.
These formulas work one cell at a time and can be filled down in older versions.
For a dynamic-array workflow in Microsoft 365 or Excel 2021 and later, you can apply TRIM to a range with =TRIM(A2:A100).
In versions without dynamic arrays, enter =TRIM(A2) in B2 and fill down.
Google Sheets also supports TRIM and SUBSTITUTE, though imported data can have the same nonbreaking-space issue.
Common edge cases
- Numbers stored as text: TRIM returns text.
If you need a numeric value for calculations, convert it deliberately, for example with=VALUE(TRIM(A2)), and check how dates or leading zeros should be handled. - Meaningful indentation: TRIM removes leading spaces.
Avoid applying it to text where indentation conveys structure. - Blank cells: TRIM applied to a blank cell returns an empty string.
A formula cell containing an empty string is not always identical to a truly empty cell. - Tabs or line breaks: TRIM does not treat every whitespace character as an ordinary space.
CLEAN can remove many control characters, but inspect the result if the source contains unusual formatting. - Comparisons still fail: If cleaned values look identical but do not match, inspect for nonbreaking spaces or other Unicode characters.
The visible appearance of text does not prove that its characters are the same.
How to check whether a cell contains extra spaces
Compare the original text with its trimmed version using =A2<>TRIM(A2).
The formula returns TRUE when TRIM changes the value and FALSE when it does not.
It detects ordinary spaces that TRIM removes, but it does not identify every hidden character.
To check a range, fill the comparison formula down alongside the data and filter for TRUE.