This tutorial explains how to utilize the TRIM function and other methods to remove additional, double, or extra spaces in Google Sheets.

When should one use the function for trimming unwanted space characters?

If you combine two physical columns and use a space delimiter and one of the columns contains blanks, there will be extra space in the combined result for that particular row.

Some of us may use QUERY to combine columns, which may result in additional, double, or extra spaces in the combined output depending on the number of columns.

In all these and similar cases, we can use the TRIM function to remove those additional, double, or extra spaces in Google Sheets.

When to use the menu commands?

There are two menu commands that you can use to remove extra spaces in Google Sheets:

1. Find and Replace.
2. Trim Whitespace.

You may want to use them when you have data that contains extra spaces due to typos or when the data is imported from external sources.

Removing Additional, Double, or Extra Spaces with the TRIM Function in Google Sheets

The TRIM function removes three types of spaces in a text: all leading spaces in the text, all trailing spaces in the text, and repeated spaces within the text.

Syntax of the TRIM Function

Syntax:

TRIM(text)

Argument:

• text: The text or reference to a cell containing the text to be trimmed.

Example:

=TRIM("  A   B    ") // returns "A B"

The TRIM formula above removes the extra spaces in the provided text.

Real-life Use of TRIM Function

You can leverage the TRIM function in conjunction with other functions or formulas, unlocking its true potential.

For instance, if cell A3 contains the first name “Jack,” B3 contains the last name “Smith,” and C3 contains the ID “1,” you can combine them using the following formula:

=A3&" "&B3&" "&C3

The result would be “Jack Smith 1.” To ensure clean formatting, it’s advisable to wrap the formula with the TRIM function. This helps in removing any additional, double, or extra spaces that may result from empty cells within the formula:

=TRIM(A3&" "&B3&" "&C3)

TRIM Function Use with ARRAYFORMULA to Remove Unwanted Spaces

In the following example, I have first names, last names, and IDs in the range A3:C10.

We can combine them in many ways, and here is the most common approach:

=ARRAYFORMULA(A3:A10&" "&B3:B10&" "&C3:C10)

A popular alternative to this is the QUERY function which you can use as follows:

=TRANSPOSE(QUERY(TRANSPOSE(A3:C10),,9^9))

Note: For a detailed breakdown of the formula, please refer to this guide: The Flexible Array Formula for Joining Columns in Google Sheets.

As you can see, the formula returns double spaces in cell D5 since the last name is missing in cell C5. There is a trailing space in cell D8 since the ID is missing, which you can’t see in the result.

Also, there are double spaces in cell D10 since the first name, last name, and ID are blank.

Use the TRIM function with the above formulas to remove those additional, double, or extra spaces in Google Sheets.

In the first formula, you should embed TRIM within the ARRAYFORMULA as it requires array formula support when using a range.

=ARRAYFORMULA(TRIM(A3:A10&" "&B3:B10&" "&C3:C10))

In the second formula, you need to use TRIM and ARRAYFORMULA.

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(A3:C10),,9^9))))

Note: You can use the LEN function in any other cell to find the number of characters in any cell (e.g., =LEN(D10))

Removing Additional, Double, or Extra Spaces with the Menu Commands in Google Sheets

Let’s begin with the Find and Replace Command. We can use two regular expressions within Find and Replace to eliminate additional, double, or extra spaces in Google Sheets.

Using Find and Replace:

Here are the steps to follow:

1. Select the cell range from which you want to remove leading spaces, trailing spaces, and all other unwanted space characters.
2. Follow one of the below methods to open the Find and Replace dialog box.
   • Use the Google Sheets keyboard shortcut Ctrl + H (Windows) or ⌘ + Shift + H (Mac).
   • Click on Edit > Find and Replace.
3. In the Find field, enter the regular expression ^\s|$\s.
4. Check the option “Search using regular expressions.”
5. Click the “Replace All” button.
6. Then, in the Find field, enter the regular expression \s+.
7. In the “Replace with” field, hit the space bar once.
8. Click “Replace All” > “Done.”

Note: This method is not applicable to remove additional, double, or extra spaces returned by formulas. In that case, you should use the TRIM function that we discussed earlier.

Using Trim Whitespace:

This is the easiest way to remove unwanted space characters. However, it is also not applicable to formula-returned results.

1. Select the range and click on Data > Data clean-up > Trim whitespace > OK.

This way, you can effectively remove additional, double, or extra spaces in Google Sheets.