How to trim whitespace in google sheets

Learn how to use the TRIM function in Google Sheets in this simple step by step tutorial.

TRIM Function in Google Sheets

  • TRIM Function in Google Sheets
  • What is the TRIM function?
  • How To Use The TRIM Function in Google Sheets
  • TRIM Formula Example
  • Summary

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

How to trim whitespace in google sheets

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.

How to Trim Whitespace in Google Sheets

There is a quick alternative way to remove the extra spaces from a cell by using the Trim whitespaces tool.

You just need to click on the cell or cells with the text to be trimmed and find the Data > Trim whitespace option in the header menu:

How to trim whitespace in google sheets

This tool will immediately remove the extra space characters from the selected cell(s).

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.

How to trim whitespace in google sheets

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.

🔥 TIP: Use CLEAN & TRIM to Tidy Your Data

If you’re cutting and pasting data from a lot of different sources, and you end up with data that’s messy and has whitespace throughout, you can use CLEAN to remove non-printable/unicode characters and TRIM to remove whitespace.

Check out more tips like this here → Google Sheets Tips

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

How do I trim white space in Google Sheets?

To trim whitespaces: Select a cell or range of data that contains whitespace on your sheet. From the toolbar, select Data > Trim whitespace. You'll then see a dialogue box that details how many cells whitespace was removed from.

How do you remove spaces in cells Google Sheets?

If you want to remove the spaces within your text and keep that data in the existing cells, you can use the handy Trim Whitespace feature. Select the cells with the text you want to remove the spaces from. Then click Data > Trim Whitespace from the menu.

How do I trim a value in Google Sheets?

TRIM is a function in Google Sheets that removes all spaces from text except for single spaces between words. This can be useful when cleaning up data or text that has been copied and pasted from other sources. The TRIM function can be accessed by typing =TRIM( in the cell where you want the text to be cleaned up.