Data imported from other systems often contains extra spaces that disrupt sorting, formulas, and lookups. These spaces can be leading, trailing, or multiple spaces between words. The TRIM function is a built-in Excel tool designed to clean this text. This article explains how to use TRIM to find and delete unwanted spaces in your data.
Key Takeaways: Using TRIM to Clean Data
- TRIM function: Removes all spaces from text except for single spaces between words.
- Find & Select > Go To Special > Blanks: Identifies cells that appear empty but may contain only space characters.
- Paste Special > Values: Replaces formulas with their cleaned results to finalize your data.
What the TRIM Function Does
The TRIM function is a text tool that standardizes spacing. It deletes any space character at the start or end of a text string. It also reduces any occurrence of multiple consecutive spaces between words down to a single space. This function is essential when data comes from web forms, databases, or other applications that may add irregular spacing.
TRIM only affects the standard space character (ASCII 32). It does not remove non-breaking spaces, which are often used in web content and appear as CHAR(160). Cleaning data may require handling these different space types. You need a basic understanding of formulas to use TRIM effectively, as it typically requires creating a helper column.
Steps to Clean Data with the TRIM Function
Follow this process to identify cells with extra spaces and clean them using a formula.
- Insert a Helper Column
Select the column immediately to the right of your data column. Right-click the column header and choose Insert. This new column will hold your cleaned results. - Enter the TRIM Formula
In the first cell of the new column, type =TRIM( and then click the first cell of your original data. Close the parenthesis and press Enter. The formula will look like =TRIM(A2). - Copy the Formula Down
Double-click the fill handle (the small square at the bottom-right corner of the formula cell) to copy the formula down the entire column. This applies TRIM to all your data rows. - Replace Original Data with Cleaned Values
Select all the cells containing the TRIM formulas. Press Ctrl+C to copy them. Then, select the first cell of your original data column. Right-click and choose Paste Special. In the Paste Special dialog, select Values and click OK. This replaces the original text with the cleaned results. - Delete the Helper Column
Right-click the header of the helper column you created and select Delete. Your worksheet now contains the cleaned data in the original column location.
Using Find and Replace for Non-Standard Spaces
If TRIM does not clean all spaces, you may have non-breaking space characters. Use Find and Replace to handle them.
- Open Find and Replace
Select your data range. Press Ctrl+H to open the Find and Replace dialog box. - Enter the Non-Breaking Space Code
In the Find what field, hold Alt and type 0160 on the numeric keypad, or type the formula =CHAR(160) in a cell, copy the resulting character, and paste it into the field. Leave the Replace with field empty. - Execute the Replace
Click Replace All. This removes all non-breaking space characters from the selected range. You can then run the TRIM function on the data.
Common Mistakes and Limitations
TRIM Does Not Remove All Space Characters
As noted, TRIM ignores the non-breaking space (CHAR(160)). Data copied from web pages often contains this character. Use the Find and Replace method described above before applying the TRIM function for complete cleaning.
Formulas Return Errors After Using TRIM
If you use TRIM on numbers stored as text, subsequent mathematical formulas may still fail. After using TRIM, you may need to convert text to numbers. Select the cleaned cells, click the warning icon that appears, and choose Convert to Number.
Data Appears Unchanged After TRIM
If you apply the TRIM formula but do not use Paste Special > Values, the original data remains. The cell shows the cleaned result, but the underlying value is still the formula referencing the dirty data. Always use Paste Special to finalize the cleaning process.
TRIM vs. CLEAN vs. Find and Replace
| Item | TRIM Function | CLEAN Function | Find and Replace |
|---|---|---|---|
| Primary Use | Remove extra standard space characters | Remove non-printable characters (ASCII 0-31) | Remove specific characters like non-breaking spaces |
| Handles Leading/Trailing Spaces | Yes | No | Yes, if you search for a space |
| Handles Multiple Internal Spaces | Reduces to one space | No | No, unless using wildcards |
| Best For | General text data cleanup from databases | Data imported from legacy systems | Targeted removal of a known problem character |
You can now clean datasets by removing unwanted spaces with the TRIM function. Combine it with Find and Replace for non-breaking spaces to handle data from web sources. For a more advanced cleanup, nest the functions by using =TRIM(CLEAN(A2)) to remove non-printable characters and extra spaces in one step. Use Flash Fill as an alternative by typing a cleaned example and pressing Ctrl+E.