In this article, we will learn how to remove spaces in Google Sheets. As we work with data, we often need to type a lot of text, numbers, and strings. And while typing in Google Sheets, we often make mistakes – some are grammatical errors, some are spelling mistakes, and some are extra, unwanted, or misplaced spaces. This is a very common error that we all make while working with Google Sheets or any other tool. Fortunately, Google Sheets understands this problem and offers many solutions to resolve it with just a few clicks.
Today, we will learn the methods that we can use to easily remove unwanted spaces in Google Sheets. These spaces can be leading spaces, trailing spaces, or double spaces.
Use Cases of Removing Spaces in Google Sheets
If you work with tools like Google Sheets and Excel, you have surely made these mistakes many times. In Word and Google Docs, we have a built-in grammar correction tool that helps us address these writing problems. The system suggests spelling corrections, detects double spaces, leading and trailing spaces, as well as redundant words. And let’s not forget about tools like Grammarly, which assist us as online writing assistants. However, the built-in grammar correction tool and even Grammarly do not work with Google Sheets or Excel.
Excel and Google Sheets are more spreadsheet editors than document writing tools. However, we still have many solutions to address the spacing problems that users leave while working on Google Sheets or Excel. We have several solutions, and we will see 3 in this article. You can use them according to your preferences, so it is important to learn how to remove spaces in Google Sheets.
How to Remove Spaces in Google Sheets
Here, we will learn all the methods step by step with examples and screenshots to teach you each method with practical examples and use cases. Let’s start with a sample dataset.
Method 1: Using the Data Cleaning feature
In this section, we will learn how to remove white spaces in Google Sheets using the data cleaning feature. This is a built-in feature in Google Sheets. We can directly remove all white spaces, but it is better to open the suggestions tab and see all the unwanted spaces with their occurrence and position, and then remove them all in one go if they are not needed. Let’s see this feature. It is a quite useful feature natively available offline.
Step 1: Sample Data
Step 2: Select all the data
Step 3: Go to Data > Data Cleaning
Step 4: Now, you can click on “cleanup suggestions,” and it will show you all the detected white spaces in your data
Note: Another option that we have is “Remove duplicates,” which is used to remove duplicates, but since it is out of context for this article, I will not explain it.
Step 5: You can click on “Clean Spaces,” and it will directly remove all extra spaces from the data. I recommend using the previous option “Cleanup suggestions” so that you can retain any intentionally added spaces.
Step 6: When you click on “Cleanup suggestions,” you now have a dialog box open on the right side.
Step 7: You can select a specific column to review all the extra spaces
Step 8: You can see the position of spaces and their count with small red lines
Step 9: You can click on Ignore (if you intentionally added the space), and it will be ignored.
Step 10: After a thorough review, you can click on the “Remove All” button to remove all the extra spaces detected by the system.
Step 11: When you hover over a column, a red indicator appears on the actual cell.
Method 2: Using the TRIM Function
TRIM is a simple function in Google Sheets that removes spaces at the beginning and end, or between each word written in a cell. In this section, we will learn how to remove spaces in Google Sheets using the TRIM function. TRIM is not a very powerful function. It can only take a single cell as input, and it does not remove line breaks inside a single cell.
But we can use this function in combination with ARRAYFORMULA and the CLEAN function.
Let’s start with the basic usage of the TRIM function.
Step 1: Sample Data
Step 2: In another cell, write the TRIM function starting with the equal sign (=)
Step 3: Pass a cell reference as a single argument. (you can also pass a direct string enclosed in double quotes)
Step 4: Press Enter, and you’re done.
Now, let’s move on to removing spaces in a range of cells.
Step 5: Pass a range of cells
Step 6: An error is returned
The error message says: Unable to find an array value.
Step 7: Combining the TRIM function with ARRAYFORMULA to avoid the above error
Step 8: Now, we can remove spaces from the range of cells (array), without any error.
Now, let’s see if this combined function can detect and remove a line break (entered in the cell by pressing Ctrl + Enter).
Step 9: Adding a line break in some cells
Step 10: Now, pass this cell into this function
Step 11: No, this function did not remove the line break (vertical spaces)
Now, let’s try the CLEAN function with this combination of formulas.
Step 12: Adding the CLEAN function inside the TRIM function, inside ARRAYFORMULA
Step 13: Press Enter and see the result
Step 14: Now, we have solved our two problems. The line break is detected and removed with the combination of the CLEAN, TRIM, and ARRAYFORMULA formulas.
Method 3: Using the Power Tools Add-on
We have already covered many add-ons in our Google Sheets tutorial series. Today, we will see another add-on called “Power Tools,” which helps us in many ways. It offers over 30 daily tasks and automated functions that assist us in every possible way. But, in the context of today’s topic, we will see the removal feature where we will learn how to remove spaces in Google Sheets using Power Tools.
To install and use this add-on, follow the steps below:
Step 1: Go to Extensions > Add-ons > Get add-ons
Step 2: Search for “Power Tools” in the Google Workspace Marketplace
Step 3: Click on the “Power Tools” app among the results.
Step 4: Click on Install
Step 5: Click on Continue
Step 6: Choose a Google account to continue the installation
Step 7: Allow the authorization
Step 8: Power Tools successfully installed. Click on Done
Step 9: Close the tab
Step 10: Go to Extensions > Power Tools > Tools > Text
Step 11: Click on Remove
Step 12: Click on Remove Spaces and Delimiters
Step 13: Check Remove leading and trailing spaces
Step 14: Check Remove spaces between words to keep only one space
Note: Also, check other options (by reading the descriptions) to use more features
Step 15: Click on the Remove button
Step 16: You can see that spaces are removed, and the data is cleaned
This is how you can use the “Power Tools” add-on to easily remove spaces and utilize many other convenient features.
Notes
FAQ
Q: How can I remove white spaces from an entire column?
A: You can simply use the TRIM function with ARRAYFORMULA to remove white spaces from a long range of cells, whether it is a single column or multiple columns. You can refer to the previous sections for that.
Q: How can I remove line breaks?
A: A function called CLEAN can be used to remove line break spaces, but to work on the range instead of a single cell, you can use ARRAYFORMULA and TRIM to create a combination of a powerful formula that will remove leading, trailing, and word spaces. It will also remove line breaks within a cell.
Q: What if I want to remove specific spaces?
A: You can use the built-in tool by going to Data > Data Cleaning > Cleanup suggestions. You can see where white spaces are detected in your data, you can ignore the ones you intentionally added, and remove all the others.
Conclusion
In summary, that’s how you can remove spaces in Google Sheets. It was a long but easy tutorial, I believe. I covered the three most practical and preferred methods. Firstly, I covered the built-in native tool to remove spaces, and it is one of the most advanced and powerful built-in tools I’ve seen in Google Sheets. Then, we learned how to remove spaces in Google Sheets using the TRIM formula, then we saw its limitations and added ARRAYFORMULA with it, then we added the CLEAN formula to enable it to also remove line breaks. That’s how we created a complete formula that can clean data in every possible way. Next, we learned how to install an add-on called “Power Tools,” which is a great application that offers more practical features for daily productivity in Google Sheets.
I hope you enjoyed this article and find it useful. For any issues, you can comment below. Please like, subscribe to our blog, and share the article with your friends on social media. Take care and keep learning with Crawlan.com.
