Yesterday, in the 30XL30D challenge, we took a poke at the lazy brother-in-law function -- AREAS. It's not used much in the working world, but you saw how the 3 reference operators work, so I hope that was useful!
For day 3 in the challenge, we'll examine the TRIM function. In January, some people are trying to TRIM a few pounds, and my Excel Calorie Counter and Excel Weight Loss Tracker workbooks are popular. Unfortunately, the TRIM function won't help with the pounds, but can remove extra spaces from a text string.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let's take a look at the TRIM information and examples, and if you have other tips or examples, please share them in the comments. And good luck with the calorie counting!
Function 03: TRIM
The TRIM function removes all the spaces in a text string, except for single spaces between words.
How Could You Use TRIM?
The TRIM function can help with the cleanup of text that you've downloaded from a website, or imported from another application. The TRIM function:
- Removes spaces from start and end of text string
- Removes all except single spaces between words
- Does NOT remove some special space characters copied from websites
The TRIM function has the following syntax:
- text is a cell reference or text string from which you want spaces removed.
The TRIM function only removes standard space characters from the text. If you copy text from a website, it might contain special non-breaking space characters, and the TRIM function will not remove those.
Example 1: Removes spaces from start and end of text string
You can use the TRIM function to remove all the space characters at the start and end of a text string. In the screenshot below, there are 2 extra spaces at the start and 2 at the end of the text in cell C5. The TRIM function in cell C7 removes those 4 spaces.
Example 2: Removes all except single spaces between words
You can use the TRIM function to extra space characters between words in a text string. In the screenshot below, there are 3 extra spaces between the words in cell C5. The TRIM function in cell C7 removes those extra spaces, as well as the 2 spaces at the start and 2 spaces at the end of the text string.
Example 3: Does NOT remove some space characters
The TRIM function does NOT remove some space characters, such as a non-breaking space copied from a website. In the screenshot below, Cell C5 contains one non-breaking space, and that is not trimmed.
You can manually delete the non-breaking space character, or use the SUBSTITUTE function or a macro. You'll see other ways to clean up your data during the 30 Excel Functions in 30 Days challenge.
Download the TRIM Function File
To see the formulas used in today's examples, you can download the TRIM function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the TRIM Video
To see a demonstration of the examples in the TRIM function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Remove Spaces With Excel TRIM Function