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.
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 ways to remove extra spaces in Google Sheets.
So let’s get started!
Remove Extra Spaces Using the TRIM Whitespace Option
Google Sheets keeps adding new functionalities every few months, and one such functionality is to remove whitespace in cells.
There is now 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 Using the TRIM Function
In most cases, using the TRIM function 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:
Copy the formula on 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).
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:
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.
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 in Google Sheets, if you don’t want to use a formula, you can use the Find and Replace technique to find and replace double spaces.
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.
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 useful!
Other Google Sheets tutorials you may also like: