Any work with text data or numbers in Google Sheets, sooner or later you will have to deal with those pesky extra spaces that find their way in the cells. But, can Google Sheets remove spaces? The simple answer is yes, and you can check out how by following this guide.
These could be leading spaces (that occur before the cell content), trailing spaces (that occur at the end of the cell content), or double space characters that occur between the text strings or numbers.
And as you might already be thinking, all of these are undesirable.
In this tutorial, I will show you a couple of really easy and neat methods of how to remove spaces in Google Sheets.
So let’s get started!
Table of Contents
The Google Sheets TRIM Whitespace Option
Google Sheets keeps adding new functionalities every few months, and one such functionality is to remove whitespace in cells.
Google Sheets can now remove whitespace with an in-built functionality that allows you to quickly remove whitespace (such as leading, trailing, or double spaces) with just two clicks.
Suppose you have the dataset as shown below and you want to quickly remove all the extra spaces.
Below are the steps to do this:
- Select the data from which you want to remove the extra spaces
- Click the Data tab
- In the options that show up, click on the ‘Trim whitespace’ option
The above steps would remove all the leading, trailing, and double spaces and show you a prompt letting you know how many spaces have been removed.
This is, by far, the easiest way to remove extra spaces in Google Sheets.
Remove Extra Spaces in Google Sheets Using the TRIM Function
In most cases, using the TRIM function in Google Sheets will take care of all the leading trailing and double spaces in your data set.
It is built for the same reason.
Suppose you have a data set of names as shown below, where there are leading spaces, trailing spaces, and double spaces in the names.
Below is the formula that will remove all these extra spaces in Google Sheets:
=TRIM(A2)
Copy the TRIM Google Sheets formula to all the cells in the column to get the result for all the names.
Why the trim function can easily remove all the extra spaces in Google Sheets, it will not be able to remove any line break (which occurs when you use ALT + ENTER to get a new line in the same cell). Google Sheets can remove trailing spaces or leading spaces with this formula.
If you want to remove extra spaces as well as the line break, you can use the below combination of the TRIM function and the CLEAN function:
=TRIM(CLEAN(A2))
The benefit of using the TRIM function is that you can combine it with formulas.
For example, if you’re using lookup formulas where the lookup value or the lookup range can have extra spaces, you can wrap these within the TRIM function to remove it and make the data consistent.
Google Sheets Remove Spaces – Find and Replace Space Characters
Sometimes, you may not want to use a formula because it would require you to have an additional column where you will get the result.
While using the TRIM function is the best way to remove extra spaces, if you don’t want to use a formula, you can use the Find and Replace technique for Google Sheets to remove leading space, trailing space, or space between.
A big limitation of this method is that you can only find double spaces, and it would not be able to remove any leading or trailing spaces in the cell.
On the plus side, in case you want to remove all kinds of spaces, be it single or double, you can do that using Find and Replace.
Suppose you have a data set as shown below and you want to remove all the double spaces between the names and replace it with a single space.
Below are the steps to do this:
- Select the cells that have the names with double space characters
- With the data selected, hold the Control key and press the H key (or Command + H if using Mac). This will open the Find and Replace dialog box
- In the Find field, enter two space characters
- In the Replace field, enter one space character
- Click on the Replace All button
- Continue clicking on the Replace All button till you see the ‘No matches found’ displayed above the button.
Using the SUBSTITUTE Function to Remove Spaces in Google Sheets
The SUBSTITUTE function works similarly to the find and replace tool as it finds data and changes it. Let’s take a look at how it works.
The above example has two undesired spaces between 123 and 456. Here is how you use SUBSTITUTE to remove those spaces.
- Type =SUBSTITUTE into an empty cell
- Click or type the cell you want to remove spaces from and press the comma key
- Use quotation marks to show the spaces you want to remove for the first argument in the formula and press the comma key
- Use a second set of quotes with no space in between and press enter
This tells Google Sheets you want the formula to substitute two space for zero spaces.
Remove Spaces in Google Sheets With an Add-On
There are several add-ons that allow you to remove spaces with one click. One example of this type of add-on is Power Tools for Google Sheets.
To use this add on:
- Install it by navigating to Extensions>Add-ons>Get add-ons and typing in Power Tools then clicking install
- Navigate to Extensions>Add-ons>Power Tools
- Click Remove
- Check Remove all spaces and click Remove again
Google Sheets Remove Spaces FAQ
How Do I Remove Blank Spaces in Google Sheets?
there are several methods but the best ones to use are the TRIM function and the Trim Whitespace option under the data menu.
How Do You Remove Special Characters and Spaces in Google Sheets?
You can use the SUBSTITUTE function to identify which special character you want to remove and what to replace it with. You could also use the Google Sheets find and replace tool for the same goal.
The above steps would remove all the double spaces and replace them with single spaces.
The drawback of this method is that it won’t be able to remove leading or trailing spaces (which could be double or single spaces).
So these are three different ways you can use to remove extra spaces in Google Sheets (leading, trailing, and double spaces).
The easiest way would be to use the TRIM whitespace option, and you can also use the TRIM formula or Find and Replace.
I hope you found this tutorial that answered the question “can Google Sheets remove spaces” useful!
Other Google Sheets tutorials you may also like:
