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!
TRIM Function
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.
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
TRIM Syntax
The TRIM function has the following syntax:
- TRIM(text)
- text is a cell reference or text string from which you want spaces removed.
TRIM Traps
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.
=TRIM(C5)
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.
=TRIM(C5)
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.
=TRIM(C5)
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.
_____________
This is going to come in very handy – Thanks, Debra!
While the Excel TRIM function is extremely useful, sometimes it does too much. There may be occasions when you do not want to reduce multiple adjacent internal spaces to single spaces but only remove leading and trailing spaces (see Outer Trim below), or you may just wish to remove only the trailing spaces (see Right Trim below) or, perhaps, only the leading spaces (see Left Trim below). Here are worksheet formulas that will do this (and note the method of entry for each)…
Outer Trim (Array-Entered**)
———————-
=MID(A1,FIND(LEFT(TRIM(A1)),A1),MAX((MID(A1&REPT(” “,99),ROW(A1:A99),1)” “)*ROW(A1:A99))-FIND(LEFT(TRIM(A1)),A1)+1)
Left Trim (Normally Entered)
———————-
=MID(A1,FIND(LEFT(TRIM(A1)),A1),LEN(A1))
Right Trim (Array-Entered**)
———————-
=LEFT(A1,MAX((MID(A1&REPT(” “,99),ROW(A1:A99),1)” “)*ROW(A1:A99)))
**Array-Entered means you commit the formula using CTRL+SHIFT+ENTER and not just Enter by itself.
@Lynda, you’re welcome! Glad it will help.
@Rick, thanks for the alternative formulas for trimming just the ends.
My data is VACCINES – PAEDIATRIC TEAM, its length is 27. if i trim the data using excel it will be 26, but it is not executing in vba. while displaying form i am using..Select distinct TRIM(UCASE([TherapyArea]))).. but it si not removing space in between Vccines and PAEDIATRIC TEAM. is there any solution?