Whenever you copy text from other sources like MS Office into Microsoft Excel, it can occasionally come with some characters that you want to prefer to remove, such as unnecessary spaces. You can manually remove these characters anytime: however, a function is available in MS Excel, TRIM. MS Excel TRIM Function lets you remove unnecessary characters easily.
Suppose you are trying to compare two columns for duplicate values that you know are there, but your formulas cannot find a single duplicate entry because of unnecessary spaces. Examples may be any, but we are focusing on the resolution only. Like, unnecessary spaces may be before or after a number, which you can find easily if you have to work with many cells. These all can be solved by the TRIM function of Microsoft Excel.
What is the TRIM Function?
TRIM is an inbuilt function of Microsoft Excel that lets you remove any extra spaces from a specific text string or a cell containing text, leaving only a single space between words.
How to use the TRIM Function in Microsoft Excel?
For example, we have data in cell A1 will multiple spaces; we want to remove that unnecessary spaces and want purified data in cell B1. Click on cell B1 and simply write “=TRIM(A1)” and press Enter from the keyboard to produce the filtered data.
The result will be like the below screenshot:-
Alternatively, to remove extra spaces from a text string directly, you can use the following formula:-
Replace the ‘Unpurified Data‘ and put your sentence or paragraph to purify it.
Press Enter from the keyboard to get the data without unnecessary spaces.
In addition to extra spaces, if your data contains line breaks and other non-printing characters, you can use the TRIM function in combination with CLEAN to delete the first 32 non-printing characters in the 7-bit ASCII code system.
For example, if you want to remove spaces, line breaks, and other unwanted characters from cell A1, use this formula:
In conclusion, removing unnecessary spaces and characters from text strings in Microsoft Excel can be a time-consuming task, especially when dealing with a large amount of data. However, the TRIM function in Microsoft Excel offers a simple solution to this problem. By using the TRIM function, users can easily remove any extra spaces from a specific text string or a cell containing text, leaving only a single space between words. Additionally, if the data contains line breaks and other non-printing characters, users can use the TRIM function in combination with CLEAN to delete these characters. Overall, the TRIM function is an essential tool for anyone working with text data in Microsoft Excel.