When you copy text to Microsoft Excel, it may occasionally contain characters that you would prefer to be removed, such as unnecessary spaces. Instead of removing it 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 extra spaces from a specific text string or from a cell containing text, leaving only one space between words.
For example, let̵
This is a text string.
This string itself contains a single space between each word. However, if there were extra spaces in your text string before the first word (eg “This is a text string.”) Or if there were multiple spaces between words (eg “This is a text string.”), Then you could use the TRIM feature to remove it 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 (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 unbreaking Unicode character (decimal value 160) is often used for spaces. TRIM will not remove this, so if this character is in your text, you will need to remove it manually.
RELATED: What are character encodings such as ANSI and Unicode, and how are they different?
How to use the TRIM function in Microsoft Excel
To help you understand how to use the TRIM function in Excel, let’s explore the two main methods of applying it in a new formula using sample data.
When you create a formula with TRIM, it has only one argument: the text itself. This can be text that you insert directly into the formula or a cell reference to a cell that contains your text.
For example, the following formula uses TRIM to remove unnecessary spaces 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 can use the following formula, replacing the sample string with your own text.
=TRIM(" This text string contains unnecessary spaces")
As mentioned earlier, TRIM is only designed to remove extra ASCII spaces (decimal value 32) from text strings. If you are still seeing unnecessary spaces, it is most likely caused by a non-breaking space (Unicode decimal value 160) in your text.
TRIM does not support these, so you will need to manually check these characters and remove them from your text if you prefer.