How to use TRIM in Google Sheets

TRIM Function in Google Sheets

When you import or copy text data into a spreadsheet, there are usually extra spaces included in the text that might affect the results of your functions. In order to work with clean data, you need to get rid of these spaces.

Rather than manually editing the data to delete the unwanted spaces, use the TRIM function to eliminate them and clean up your text.

What is the TRIM function?

The TRIM function removes leading, trailing, and repeated spaces in the text. It returns a string based on a given string, after removing the extra spaces.

The way you can use the TRIM function is:

=TRIM(text)

The function has one argument, text. This is the string that you would like to trim.

As a result, the TRIM function removes all spaces before the first character, all spaces after the last character, and all duplicate spaces between characters, leaving only single spaces between the words.

When to use TRIM for marketing stuff?

• Removing extra (and unwanted) spaces from the end of URLs.
• Tidying messy data (to prevent issues with other formulas)
• For clean and consistent URLs.

How To Use The TRIM Function in Google Sheets

You can define the text argument in two ways:

1. Direct text value
2. Cell reference

Direct text value

• Put the actual text to be trimmed into the function as the text argument.
• If you do this, the text must be enclosed in double quotation marks, for example:

=TRIM("This text has some extra spaces that I want to remove. ")

Cell reference

• Reference a cell of the sheet that contains the text to be trimmed.
• In this case, you must write the function into another cell than the original data.
• Write the following function with the correct reference:

=TRIM(A2)

In this second example, you’ll need to create a new column or row in the sheet for your clean text, but often you only need the trimmed text.

Then you can copy and paste the clean text back to the original column to replace it.

Even though this simple tool exists to remove extra spaces, the TRIM function has more potential when you use it in combination with other functions…

TRIM Formula Example

For example, you can include it in your formulas to check for duplicate entries, to search for a specific term or to avoid false results when working with numbers.

Whenever the possible extra spaces may cause errors, you can solve this issue by completing your textual or numeric input values with a wrapping TRIM function inside your other functions.

Here is an example of two (bacon-flavoured) texts that are almost the same, but one has extra spaces.

Applying the EQ (equality) formula would normally return FALSE as the result, even though the two texts only differ in the number of spaces.

=EQ(TRIM(B29),TRIM(D29))

You can use a wrapping TRIM function for every text argument in the function. This way the formula will work with clean data and ignore the spaces when checking for the textual equality.

Similarly, you can add the TRIM function to any other formulas to make sure that unwanted spaces will not manipulate your results.

Summary

There you have it, a simple but useful Google Sheets formulas for tidying up data.

You might also like these resources:

• How to extract domain from URL
• Check out the Google Sheets Templates in the directory