How to use the TRIM Function in Microsoft Excel?

Posted by

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.

Excel Tip Use TRIM To Cleanse Your ...
Excel Tip Use TRIM To Cleanse Your Data Set

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 on 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:-

=TRIM("Unpurified Data")

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, you want to remove spaces, line breaks, and other unwanted characters from cell A1, use this formula:


Press Enter from the keyboard to get the result. That’s all!

Leave a Reply

Your email address will not be published. Required fields are marked *