How to Use the TRIM Function in Google Sheets

Working with spreadsheets that have excessive white space is tedious and can interfere with certain functions. However, you can easily fix this in Google Sheets by using the TRIM function or the trim spaces shortcut in the menu. Read on to learn how.

What is the TRIM function in Google Sheets?

Copying or importing text into a spreadsheet often adds extra spaces to the text, which can affect the results of your functions. You need to close these gaps to deal with clean data.

You can use a TRIM formula to get rid of the extra spaces and clean up your text instead of manually altering the data to remove them. TRIM can remove the leading, trailing and repeating spaces of the text area. After deleting the excess spaces, a new text string is created based on the string originally provided.

You may not even need to use the TRIM function to sort out your excess white space. You can often use the trim spaces shortcut which is much easier to use.

You can access this feature by just following a few simple steps. Here’s what you need to do:

1. Select the cells you want to clean. The blue border indicates this.
2. Click on Data in the main bar at the top of your spreadsheet. This will open a drop down menu.
3. Click on data cleansing.
4. Click on Cut off spaces.

After clicking this option, Google Sheets will process the data in the selected cells and apply the changes. This option is great for one-time use because the formula doesn’t apply to cells, saving unnecessary calculations and preventing the spreadsheet from becoming slow.

Syntax of the TRIM function

If the menu shortcut for trimming spaces doesn’t do what you need, you’ll need to use the TRIM function instead. The formula for TRIM in Google Sheets is pretty simple as it only takes one parameter. Here is the syntax:

=TRIM(text)

That text The parameter in this formula defines the text that needs to be truncated or its cell address. To leave single spaces between words, TRIM removes all spaces before the first character, spaces after the last character, and any double spaces in between.

How to use the TRIM function in Google Sheets

The feature also allows you to import data from another sheet or part of the same spreadsheet. Let’s take a look at the latter. Here are the steps you need to follow:

1. Click on the cell where you want to write the formula. In this case it is a cell D2.
2. Enter one equal (=) sign to start the formula.
3. Enter the beginning part of the formula, viz TRIM(.
4. Enter the address for the cell that you want to use the TRIM function on. It’s better to use the first cell here, because then you can use autofill. We will explain more about this below. In this case we used cell A2.
5. Add the closing parenthesis to complete the formula.
6. Press the Enter button to run the formula.

You will see that the value of the added cell is displayed with all unnecessary spaces removed. But you most likely don’t need to sort just one value. Spreadsheets can contain thousands of entries for a column, and it’s just not realistic to sort them all individually. Instead, we can use the handy autofill feature in Google Sheets.

To use it, select the cell, click and drag the blue dot in the bottom right corner of the box. Drag towards the data. The formula will be automatically applied to all cells there.

Advanced Use Case: Creating a nested TRIM function in Google Sheets

As with most other functions in Google Sheets, you can also use TRIM alongside other functions. To demonstrate this, let’s look at how we can use the ARRAYFORMULA, SPLIT, and TRIM functions to clean up the data and sort it into different cells. Before we begin, let’s look at the syntax for these formulas.

The syntax for ARRAYFORMULA in Google Sheets is:

=ARRAYFORMULA(formula)

The parameter can be a range, an expression that uses one or more ranges of cells, or a function whose result can be larger than one cell.

The syntax for SPLIT in Google Sheets is:

=SPLIT(text, delimiter)

That text The parameter defines the area containing the text to be split and the delimiter is the character used to separate the text, such as a space or a hyphen.

In the following example we want to split the text:

• Diane Barrett, Monica Webster, Travis Lopez, Lawrence Briggs, Steven Rose, Connie West, Luz Herrera

As you can see, there are extra spaces, and all the text appears in a single cell. To use nested functions to split the data:

1. Click on the cell where you want the first name to appear B3 in the example above.
2. Enter a equal (=) icon to start the formula.
3. The first part of the formula will be an array, so type ARRAY FORMULA( First.
4. Add the formula for TRIM. Type TRIM( here.
5. Add to SPLITS( to start the SPLIT formula.
6. Here we add the two parameters needed for the SPLIT formula. In the example above, we entered A1 as the text Argument that is the cell address that contains the names.
7. Add a comma.
8. Enter the delimiter in quotes, which will look like this “”,
9. Now add the closing brackets to complete the formula. Note: If you have a nested formula, you must add the same number of closing parentheses as the number of functions in the formula. In the example above, there are three parentheses.
10. Press Enter.

How the nested formula works

The finished formula in this particular example looks like this:

=ARRAYFORMULA(TRIM(SPLIT(A1,",")))

Once you press the Enter click, you will see that the names have been separated correctly, using the comma as the delimiter (the delimiter). TRIM removes the spaces before and after the names, and ARRAYFORMULA ensures that the names are added to the correct cells, ie they become an array.

Using the TRIM function in Google Sheets

Although it’s not often that you need to use nested TRIM functions, copied data often contains unwanted spaces. So you should familiarize yourself with the menu shortcut for trimming spaces and the TRIM feature as a stepping stone to improving your Google spreadsheets.