Google Sheets is an excellent spreadsheet program for data organization. You can easily remove leading, trailing, and double spaces between text strings or numbers using the Find and Replace Tool and functions like the TRIM.
Extra space at the beginning, end, or middle of the text string becomes a severe problem while performing various calculations.
This is a quick guide where we will discuss the fastest steps to remove spaces in Google Sheets. Let us learn various methods to trim extra spaces along with their benefits and drawbacks.
Download Example Google Sheet
Before we jump into the article, make sure you download the following spreadsheet with example data used to demonstrate the steps to remove extra spaces.
Note that the methods discussed below are pretty straightforward, but practice is the key to success.
If you have your own dataset ready to practice the examples discussed below, then skip downloading the above file.
How To Remove Spaces In Google Sheets
An extra space can be anywhere, at the beginning, end, or in between letters or numbers of the cell.
Google Sheets offers a built-in tool to identify and remove extra spaces. However, the use cases of this particular tool are limited to basic scenarios.
In this section, we will discuss the built-in tool and a few functions used to remove extra and multiple spaces.
Click on the following links to jump to that particular section of the article:
- Using Data Cleanup Tool
- Using the Find and Replace Tool
- Using the TRIM Function
- Using the SUBSTITUTE Function
Now, without any further ado, let’s start discussing each method in detail.
METHOD #1 – Google Sheets Trim Whitespace (Built-in Data Cleanup Tool)
As discussed earlier, the in-built trim whitespace or data cleanup tool is helpful for simple scenarios.
Consider the following example where we have employee names in column A.
You may have noticed multiple white spaces between a few employee’s first and last names. Our task is to remove those extra spaces using the Trim Whitespace tool.
Let’s begin,
- Open the desired Google Sheet
- Select the dataset, including extra spaces
(You can select the entire column or table range depending on your dataset) - Hover to the main menu
- Click on the “Data” tab
- Select the “Data Cleanup” option from the popup
- Next, click the “Trim whitespace” option
It is the quickest method to remove extra leading, trailing, or double spaces in Google Sheets.
METHOD #2 – Using the Find and Replace Tool
The Find and Replace may be the first tool that comes to the mind of a beginner or intermediate Google Sheets user when it comes to removing extra spaces.
Consider the following example where we have double spaces between the employee names.
Let us remove them using the Find and Replace tool in Google Sheets.
- Open the desired Google Sheets
- Select the entire dataset
(In this case, I am selecting the whole column A as it holds the employee names) - Hover to the main menu
- Click on the “Edit” tab
- Choose the last option named “Find and replace”, as shown in the following image
- A new popup will appear on your screen
- Click the empty box beside the Find option. Refer to the following image
- Type “ “ using the Spacebar on your keyboard
(You need to enter two blank spaces) - Next, click the empty box beside the “Replace with” option
- Enter “ “ using the Spacebar on your keyboard
- Make sure the value “Specific range” is selected in the Search dropdown. Refer to the following image
- Click the “Replace all” button
All double spaces between the first and last names will be replaced with single spaces, as shown in the following GIF.
Compared to other methods on this list, the Find and Replace method is helpful in case of multiple spaces. Simply put, the method allows you to find double or multiple spaces from the given range and replace it with a single space.
If your dataset contains double or multiple spaces, then this is the simplest and quickest method to get rid of them precisely.
METHOD #3 – Using the TRIM Function
In this method, let us learn to use the TRIM function to remove extra spaces in Google Sheets.
It is a simple function used to remove leading or trailing spaces in Google Sheets. You can also use the function to remove multiple spaces between words.
The general syntax for the TRIM function is as follows,
=TRIM(text)It includes a single argument named “text”. You need to replace it with the cell reference holding the text string. For example, if the cell A1 contains text string with extra spaces, then our formula will be as follows:
=TRIM(A1)Now, let us learn the steps to use the TRIM function to remove extra spaces from the given dataset.
Consider the following example,
Here, we have the employee’s first and last name in column A. The dataset contains extra spaces at various locations of the respective cells.
Let’s begin,
- Click on the cell “B2”
- Type “=trim”
- Select the first option from the popup or press “Tab” on your keyboard
- Provide the cell reference as “A2” in the place of the text argument of the TRIM function
- Complete the bracket using “)”
- Press the “Enter” on your keyboard
Google Sheets will instantly remove all the extra spaces, including leading and trailing, as shown in the above GIF.
METHOD #4 – Using the SUBSTITUTE Function
This is the final method on this list. It is similar to the Find and Replace tool method discussed above.
You can remove double or multiple spaces using the SUBSTITUTE function.
Before we jump into the steps, let us first discuss the function.
Explained: The SUBSTITUTE Function in Google Sheets
It is used to find and replace a specific text string or number from the given cell. The general syntax for the function is as follows:
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])Each argument of the above formula needs to be replaced with the proper information.
- “text_to_search” – It needs to be replaced with the cell reference holding the text string
- “search_for” – Here, you need to enter the text or number that is to be searched
- “replace_with” – It is the text you wish to insert in place of the text or number found by the previous argument
- “occurrence_number” – This is the final one used to define and target specific occurrences of the search_for argument. It is optional and replaces all the occurrences of the search_for by default
Make sure you use double quotation marks to define values in place of the second and third arguments of the function.
Now, let us use the SUBSTITUTE function to find and replace extra spaces in Google Sheets.
Consider the following example where we have spaces between the first and last names of the employees.
Here are the steps,
- Click on the cell “B2”
- Type “=substitute”
- Select the first option from the popup or press the “Tab” on your keyboard
- Replace the text_to_search argument with the cell reference “A2”
- Press “,” on your keyboard to move to the next argument
- Next, type “ “ using the Spacebar on your keyboard
(Note that you need to put two spaces between the double quotation marks) - Press “,”
- Type “ “ in the place of replace_with argument
(Note that you need to put a single space between the double quotation marks) - Complete the parentheses using the “)” on your keyboard
- Press the “Enter” key
As you can see in the above GIF, all of the double spaces between the employee’s first and last name are replaced instantly.
FAQs
Q. How do I get rid of white space in Google Sheets?
A white space or whitespace in Google Sheets represents extra spaces at the end, beginning, or between words. It can also be present between the lines.
You can use the built-in Trim Whitespace tool to get rid of them.
The steps to use the tool are mentioned in the above section of this article.
Note that apart from the Trim Whitespace tool, you can also use the Find and Replace tool to remove the white space.
Q. How do I remove line spacing in Google Sheets?
You can use the TRIM function to remove line breaks or spacing in Google Sheets.
For example, consider the following image, where we have a cell containing multiple sentences separated by double spaces.
In this case, we need to combine the TRIM function with the CLEAN function.
The final formula would be as follows,
=TRIM(CLEAN(A2))Conclusion
That’s it! You’ve made it to the end.
Removing blank, white, or extra spaces in Google Sheets is pretty straightforward. You have four different options, as discussed above. You need to choose one of them based on a particular scenario.
The TRIM Whitespace or the Data Cleanup tool is the quickest and easiest one.
Let us know if you are stuck somewhere or have any particular issues using the methods discussed in this article.
For more such tips and tricks to use Google Sheets like a pro, make sure to refer to our blog section.