The TRIM function in Excel

The TRIM function is another handy feature designed to make your life easier when working with strings in Excel. In short, TRIM eliminates trailing spaces from the end or beginning of a string, and reduces the number of spaces in between words in a string to one.

Use TRIM to clean up data so that you can easily manipulate it using Excel's other string functions.

Using TRIM

The syntax for the TRIM function is as follows:

=TRIM(text)

Pretty simple! Just feed in a string of text, and TRIM will ensure that all excess spaces are eliminated. Here are a couple of examples to demonstrate the power of this function:

=TRIM("   Excess   ")
Output: "Excess"

=TRIM("Spaces   ")
Output: "Spaces"

TRIM also cleans out excess spaces in the middle of words, like so:

=TRIM(" There   are many    excess  spaces   in this text.  ")
Output: "There are many excess spaces in this text."

As with all other string functions, you can use TRIM on a static string — like we do in the examples above — or, you can point it to a cell reference, like A5, to work its magic on the contents of the given cell.

Why use it?

Trimming the spaces off of a string of text makes things look pretty, but why would you ever want to use it in a practical context? It turns out, the TRIM function has an important use:

If you are working with a data set of your own, you can be fairly confident that it is well-organized, well-structured, and clean. But oftentimes, you'll receive a data set from somebody else that isn't as clean as you want it to be — either because it wasn't originally put together well, or because it is based off of a data source with lots of extra characters like trailing spaces at the end of strings.

This normally wouldn't be a problem, but if you want to use any of Excel's more compex functions — like the SEARCH function or lookup functions — you'll need to be sure that strings match exactly throughout your spreadsheet and are clean and easy-to-read.

Imagine, for example, that you're trying to perform a VLOOKUP, but it doesn't work because the string that you're looking up against has a few trailing spaces at the end of it. You can save yourself a significant amount of troubleshooting time up front by using TRIM on your strings to ensure that they are standardized and as easy as possible to manipulate.

Comments