HomeGoogle DocsSpreadsheetHow to Remove Additional, Double, or Extra Spaces in Google Sheets

How to Remove Additional, Double, or Extra Spaces in Google Sheets

Published on

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)
Array formula returns extra spaces in Google Sheets

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:

Regular expression to trim/remove leading, trailing, and extra spaces in Google Sheets

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.
  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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here