Excel TRIM Formula Examples for Cleaning Messy Text

In Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in A2 and reduce repeated spaces between words to one. For example, text entered as “ North Ridge ” becomes “North Ridge”; TRIM is useful for cleaning imported lists, names, and labels before sorting, matching, or reporting.

What Excel’s TRIM function does

TRIM has one argument: the text you want to clean. It removes the standard space character (ASCII character 32) from the start and end of text, and reduces runs of that same character inside the text to a single space. It does not remove every character that may look like a space, nor does it remove punctuation or change capitalization.

That distinction matters when information comes from websites, PDFs, databases, or other systems. A cell can look as if it contains a normal space while actually containing a nonbreaking space or another Unicode whitespace character. TRIM may leave that character untouched. Excel’s TRIM function is available in Microsoft 365 and earlier desktop versions, and its basic syntax is the same in Excel for Windows, Mac, and the web.

Basic TRIM formula examples

Suppose a worksheet has uncleaned text in column A, beginning in A2. Enter the formula in B2 and fill it down to clean the corresponding rows. These examples show the input layout and the expected result:

With A2 containing North Ridge , use =TRIM(A2). The result is North Ridge: the extra spaces at the edges disappear, and the three spaces between the words become one.
With A2 containing 00427 , use =TRIM(A2). The result is the text 00427. TRIM does not convert text to a number, so the leading zero remains.
With A2 containing single with no extra spaces, use =TRIM(A2). The result remains single. Applying TRIM to already clean text is generally harmless.

To use the formula, select an empty cell, type it, and press Enter. In Excel for Windows, use the fill handle at the lower-right corner of the selected cell to copy the formula down. On Mac and Excel for the web, the fill handle works similarly; the formula itself does not change. Keep the original column until you have checked the cleaned output.

Combine TRIM with other text operations

TRIM is especially handy when joining pieces of text. Without cleanup, joining a first name and surname can leave awkward gaps. With first names in A2 and surnames in B2, enter =TRIM(A2&" "&B2) in C2. If A2 contains Ada and B2 contains Lovelace , the result is Ada Lovelace. The formula inserts one separator, and TRIM removes any extra ordinary spaces around the combined text.

This approach also handles one blank name cell gracefully: the inserted separator may create extra spaces at the beginning or end, which TRIM removes. If both cells are blank or contain only ordinary spaces, the result is blank. If you need a separator only when both names are present, use a conditional formula instead; that is a separate requirement from trimming.

You can also compare text lengths to see how many ordinary spaces TRIM removed. With the original text in A2, use =LEN(A2)-LEN(TRIM(A2)). If A2 contains red fox , LEN counts 11 characters before cleaning and 6 afterward, so the formula returns 5. This counts both extra spaces within the text and spaces at its edges. It is a useful diagnostic, but it will not detect nonbreaking spaces that TRIM leaves unchanged.

Handle nonbreaking spaces and line breaks

A common reason TRIM appears not to work is a nonbreaking space, often introduced when copying text from a web page. It resembles a normal space but is a different character. For the common nonbreaking space represented by character 160, you can replace it with an ordinary space before trimming.

With text in A2 that contains ordinary spaces plus a nonbreaking space between “Blue” and “Lake,” use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). The result is Blue Lake. SUBSTITUTE changes each character 160 to a standard space, and TRIM then removes extra standard spaces. CHAR(160) is a practical fix for this familiar case, but it does not identify every possible Unicode whitespace character.

Line breaks are another separate issue. CLEAN removes many nonprinting control characters, including common line breaks, but removing a line break can join words together. If you want a line break in A2 to become a word separator, use =TRIM(SUBSTITUTE(A2,CHAR(10)," ")). If A2 contains AB, a line break, then CD, the result is AB CD. CHAR(10) represents a line-feed character. For data containing other control characters, CLEAN may be appropriate, but inspect the result: it does not automatically replace every removed character with a space.

Keep blank-looking cells blank

TRIM applied to a cell containing only ordinary spaces returns an empty text value. Sometimes that is exactly what you want. If a downstream formula or report needs an explicit blank result whenever the source has no text beyond spaces, an IF check makes that intent clear.

With the source text in A2, use =IF(TRIM(A2)="","",TRIM(A2)). If A2 contains only ordinary spaces, the result is an empty string; if it contains Cedar Lane , the result is Cedar Lane. This formula still does not treat a nonbreaking space as ordinary whitespace, so such a cell may not count as blank until you replace that character as well.

An empty string returned by a formula is not identical in every respect to a truly empty cell. For example, the formula cell still contains a formula, even if it appears blank. Keep that in mind when using counts, filters, or other formulas that distinguish empty cells from formula results.

Common problems and limitations

The spaces remain after TRIM. Check whether they are nonbreaking spaces or other Unicode characters rather than ordinary spaces. A formula bar may not make the difference obvious; replacing character 160 is a useful first troubleshooting step for copied web text.
Words run together after cleanup. CLEAN can remove line breaks or control characters without inserting separators. If the removed character marks a boundary between words, replace it with a regular space before applying TRIM.
A number looks different after cleaning. TRIM returns text. It does not standardize numeric formatting, dates, or number values. For instance, preserving an identifier such as 00427 is often desirable; for calculations, convert valid numeric text separately and check that conversion is appropriate.
Matching still fails. TRIM can resolve extra ordinary spaces, but it does not make uppercase and lowercase identical in every comparison context, remove all hidden characters, or standardize spelling and punctuation. Clean both the lookup value and the source list consistently, and investigate the actual characters if results still differ.

Apply the cleanup to a whole column

A formula creates a cleaned version of the source; it does not alter the original cell. That is safer while checking results. A practical workflow is:

Keep a backup of the original data, especially if it came from an external system.
Add a temporary column beside the text you want to clean and enter a TRIM formula in the first data row.
Fill the formula down, then review a sample of the results, including long values, blank-looking entries, and text copied from outside Excel.
If the results are correct and you want to replace the source values, copy the cleaned cells and use Paste Special > Values in the destination. This replaces formulas with their displayed results.
Remove the temporary column only after confirming that important identifiers, leading zeros, and intended spacing have been preserved.

On Windows, Ctrl+C copies the selected cells; Ctrl+Alt+V opens the Paste Special dialog, where you can choose Values. On Mac, use Command+C and then Edit > Paste Special > Values where that menu option is available. Excel for the web provides paste options after copying, though the exact dialog differs. If unsure, paste into a separate column first and verify the result before replacing source data.

TRIM compared with related Excel functions

TRIM, CLEAN, and SUBSTITUTE address different problems. TRIM reduces extra standard spaces. CLEAN removes many nonprinting ASCII control characters, such as common line-feed characters. SUBSTITUTE replaces a specified piece of text or character with another. Combining them can help, but the order and replacement choice affect the result.

For example, replacing a known line break with a space and then applying TRIM preserves a word boundary. Running CLEAN alone may remove the break and join adjacent words. Replacing a nonbreaking space with an ordinary space and then applying TRIM handles the common web-copying case. None of these functions is a universal “remove all invisible characters” tool; unusual data may require identifying its exact character codes and handling each one deliberately.

When only leading and trailing spaces should be removed while repeated spaces inside a phrase must remain, TRIM is not the right direct choice: it also collapses internal runs of ordinary spaces. Consider whether those repeated spaces carry meaning before applying it to formatted text, fixed-width data, or text where spacing is intentional.

Frequently Asked Questions

Does Excel TRIM remove all spaces?
It removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. It does not remove every kind of whitespace, including the common nonbreaking space, without additional cleanup.

Why does TRIM not remove spaces copied from a website?
The copied character may be a nonbreaking space rather than the standard space character TRIM handles. For the common character 160, replace it with an ordinary space using SUBSTITUTE and CHAR, then apply TRIM.

Does TRIM change numbers or dates?
TRIM returns cleaned text; it does not itself convert text to a numeric value or date. If the source is an identifier, retaining text can preserve leading zeros. Convert only when the data is meant to be used as a number or date.

Can I remove only spaces at the beginning and end?
TRIM also reduces multiple ordinary spaces inside the text to one. If internal spacing must remain exactly as entered, use a different, more targeted approach rather than applying TRIM to the entire value.

Can TRIM clean a full column automatically?
Enter a formula in a neighboring column and fill it down; the source cells are not changed automatically. Once you have checked the results, copy the cleaned output and paste values if you need a static replacement.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.