TRIM Function in Excel 2007

By Darrin Koltow

The purpose of Excel 2007's TRIM function is to remove all blank, unnecessary spaces at the start and end of a string. Unwanted spaces include extra spaces, tabs, and other characters that don't print. Without the TRIM function, Excel programmers would have to write code whose sole purpose was to remove unwanted blank spaces from input text. The availability of a built-in TRIM function frees programmers to concentrate on what to do with the input text.

Function Entry

You can place the TRIM function in a spreadsheet by clicking the "Formulas" tab, clicking the "Text" button, then clicking the "TRIM" item in the list that appears. You can also type the function manually, but be sure to preface your entry with the "=" sign, which tells Excel that you're entering a function, and not plain text.

How It's Used

The following shows one way of using the TRIM function. Cell A1 in an Excel spreadsheet has the following text: " John Doe." Cell B1 has the function "=trim(a1)." after the programmer types that function into cell B1, that cell will display "John Doe."

Related Functions

The TRIM function is sometimes used with other text functions, including "len." The len function calculates the length of the string in its argument. For example, "Len("John Doe")" returns 8. Any blank space before or after this string will cause len to return the wrong number of characters. Using TRIM solves this problem. For example, a programmer could write "len (trim(a1)) to ensure that len doesn't count blank spaces.

Caveats

TRIM is only one of several Excel functions that clean text that a user types in. TRIM by itself falls far short of being complete text validation processing. For example, if a user who is prompted for her email address enters "JaneDoeAtYahoo.com," TRIM won't flag this input as invalid.

Trim in Visual Basic for Applications

The TRIM function available for placing in Excel spreadsheets isn't the only TRIM function Excel offers. Excel's programming language, Visual Basic for Applications, also has a TRIM function. Like the other TRIM function, Visual Basic's function also takes a single argument, the text from which to trim blank space. The following is a short sample program using this function.public sub trimSomething()msgbox (trim(" No whitespace here! "))end sub

References & Resources