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.
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.
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.
- › 9 Basic Google Sheets Functions You Should Know
- › How to Count Characters in a Cell in Google Sheets
- › 12 Basic Excel Functions Everybody Should Know
- › 13 Essential Excel Functions for Data Entry
- › Functions vs. Formulas in Microsoft Excel: What’s the Difference?
- › 9 Useful Microsoft Excel Functions for Working With Text
- › How to Remove Spaces in Microsoft Excel
- › What to Do If You Drop Your Smartphone in the Ocean