Mastering the Art of Clean Data: A Step-by-Step Guide to Applying the TRIM Function in MS Excel

Quick Links

• What Is the TRIM Function?
• How to Use the TRIM Function in Microsoft Excel

When you copy text into Microsoft Excel, it can occasionally come with characters you’d prefer to see removed, such as unnecessary spaces. Rather than remove these manually, you can use the TRIM function to do it for you.

What Is the TRIM Function?

TRIM is a very simple Microsoft Excel function that removes any extra spaces from a specific text string or from a cell containing text, leaving only a single space between words.

For instance, let’s assume that a cell in Excel contains a text string like this:

This is a text string.

This string itself contains a single space between each word. However, if there were additional spaces in your text string before the first word (eg. “ This is a text string.”) or if it had multiple spaces between words (eg. “This is a text string.”), then you could use the TRIM function to remove these for you.

While you could do this manually, TRIM allows you to quickly remove unnecessary spaces from large sets of data, saving you time in the process.

There is one limitation, however. TRIM removes the ASCII space character (decimal value 32) from text strings, which is the standard space character used in most text.

The exception to this is for text on web pages, where the Unicode non-breaking space character (decimal value 160) is often used for spaces. TRIM won’t remove this, so if this character exists in your text, you’ll need to remove it manually.

Related: What Are Character Encodings Like ANSI and Unicode, and How Do They Differ?

How to Use the TRIM Function in Microsoft Excel

To help you understand how to use the TRIM function in Excel, we’ll explore the two main methods for applying it in a new formula using sample data.

If you’re creating a formula using TRIM, it has only a single argument—the text itself. This can be text you insert into the formula directly or a cell reference to a cell that contains your text.

For example, the following formula uses TRIM to remove unnecessary space characters from cell A2. To use this formula yourself, replace the cell reference “A2” with your own.

=TRIM(A2)

Advanced Find and Replace for Google Sheets, Lifetime subscription

To remove extra spaces from a text string directly, you could use the following formula, replacing the example string with your own text.

=TRIM(“ This text string contains unnecessary spaces”)

As previously mentioned, TRIM is only designed to remove additional ASCII space characters (decimal value 32) from text strings. If you’re still seeing unnecessary spaces, this is likely caused by a non-breaking space character (Unicode decimal value 160) in your text.

TRIM doesn’t support these, so you’ll need to manually check and remove these characters from your text if you’d prefer to use it.

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |