How to Remove Spaces in Google Sheets [3 Methods] -

Key Takeaways: How to Remove Spaces in Google Sheets

To Remove Spaces in Google Sheets

Select your data.
Go to “Data” in the menu.
Choose “Data Clean-up“.
Click “Clean-up suggestions” to view detected spaces.
Review spaces and click “Trim whitespace” to remove them.

In a new cell, type “=TRIM(“Enter the cell reference containing the text you want to clean.
Close the function with “)” and press Enter.

To remove spaces in a range, use “ARRAYFORMULA(TRIM(” in another cell.
Add “CLEAN(” inside the function to remove line breaks.
Press Enter to apply the formula.

Go to “Extensions” > “Add-ons” > “Get Add-ons“.
Search for “Power Tools” and click on it.
Select “Install” and grant permissions.
Open “Extensions” > “Power Tools” > “Tools” > “Text”.
Click on “Remove” and choose “Remove spaces and delimiters“.
Check “Remove leading and trailing spaces” and “Remove spaces between words“.
Click “Remove” to clean your data.

In this article, we will learn how to remove space in google sheets.

We mostly work with data and we need to type a lot of texts, numbers, and Strings. While typing in google sheets, we humans make many mistakes, some of them are grammar mistakes, some of them are spelling mistakes, and some of them are the mistakes of extra spaces, unwanted spaces, and spaces in the wrong places. This is a very common mistake we all do when working with google sheets or any other tool. Fortunately, google sheets understand this problem and therefore google sheets offer many solutions to solve this problem in a few clicks.

Today we are going to learn the methods using them we can easily remove unwanted spaces in google sheets. These spaces can be leading, trailing, or double spaces.

Table of Contents

Use Case of Removing Spaces in Google Sheets

If you work with tools like google sheets and excel, you have done these mistakes many times. In words, and google docs, we have a built-in grammar checking tool that assists us to fix these writing problems. The system suggests spelling corrections, detects double spaces, trailing and leading spaces, and also detect redundant words. And do not forget to tools like Grammarly, which help us as online writing assistants. But, the built-in grammar tool and even Grammarly does not work with google sheets or excel.

Because excel and google sheets are more likely spreadsheet editors, not document writing tools. Well, we still have a lot of available solutions to solve the spacing problems that are left by users while working on google sheets or excel. We have many solutions we will see 5 solutions in this article. You can use them as your preferences; therefore, we need 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 easy examples and screenshots to teach you each method with practical examples and use cases. Let’s start with a sample dummy dataset.

How to Remove Spaces in Google Sheets – Using Data Clean-up Feature

In this section, we will learn how to remove white spaces in google sheets using the data cleanup feature. It’s a built-in feature in google sheets. We can directly remove all the whitespaces, but a better practice is to open the suggestions tab and see all the unwanted spaces with their occurrence and position and remove them one by one altogether if nothing is wanted. Let’s see this feature. It’s a quite useful function available offline natively.

For this example, I have a data set of strings and the error name. I have kept some mistakes of spacing in the String column, we will see how to manipulate this data and how to remove spaces in google sheets.

Step 1

Sample data

Step 2

Select all the data

Step 3

Go to Data > Data Clean-up

Step 4

Now you can click on “clean-up suggestions”, and it will show you all the detected whitespaces in your data

Note: Another option we have is “Remove Duplicates” it is used to remove duplicates since it’s out of the context of this article so I am not going to explain it,

Step 5

You can click on “Trim whitespace”, and it will directly remove all the extra whitespaces from the data, I will recommend you to use the previous option “clean-up suggestion” so that you can save any spaces you have added deliberately.

Step 6

As you clicked on “clean-up suggestions”, now you have a dialog box opened on the right side.

Step 7

You can select a specific column to overview all the extra whitespaces

Step 8

You can see the position of the whitespaces and the number of them by small red lines

Step 9

You can click on ignore (if you added the whitespace deliberately) and, it will be ignored.

Step 10

After a complete overview, you can click on the trim all button to trim all the extra whitespaces detected by the system.

Step 11

You can see when you mouseover on any column it indicated a red indication on the actual cell.

How to Remove Spaces in Google Sheets – Using Trim Function

Trim is a simple function in google sheets that trim the whitespaces from the start, and end or every word written in the 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 take only one cell as an input, and it does not remove a line break within a single cell.

But we can use this function with the combination of ARRAYFORMULA and CLEAN function.

Let’s start with the basic usage of the Trim function.

Step 1

Sample dataset

Step 2

In any other cell write the Trim function starting with an = sign.

Step 3

Pass a cell reference as the only argument. (you can also pass a direct String inside double quotes)

Step 4

Hit Enter, and you’re done.

Now let’s pass a range and see what happens.

Step 5

Passing a range of cells

Step 6

An error returned

The error says: An Array value could not be found.

Step 7

Combining the Trim function with ARRAYFORMULA, to avoid the above error

Step 8

Now we can remove spaces from the cell range (Array), without any error.

Now let’s see if this combined function can detect and remove a line break (occurred within the cell by pressing Ctrl + Enter)

Step 9

Adding a line break in some cell

Step 10

Now pass this cell in this function

Step 11

No, this function did not remove the line break (vertical spaces)

Let’s try the CLEAN function with this combination of the formulas.

Step 12

Adding CLEAN function inside the TRIM function, inside the ARRAYFORMULA

Step 13

Hit Enter key, and see the result

Step 14

Now we have solved our both problems. The line break is detected and removed with the combination of CLEAN TRIM and ARRAYFORMULA

How to Remove Spaces in Google Sheets – Using Power Tools Add-on

We have already covered many add-ons in our series of google sheets tutorials. today we will see another add-on named “Power Tools“, which helps us in many ways. It has 30+ day-to-day tasks and automatic functions that help us by any means. But, in the context of today’s topic, we will the remove function in which we will learn how to space in google sheets using power tools.

To install and use this add-on follow the below steps

Step 1

Go to Extensions > Add-ons > Get Add-ons

Step 2

Search “Power Tools” in the Google Workspace Marketplace

Step 3

Click on the “Power Tools” app from 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 permission

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 on Remove leading and trailing spaces

Step 14

Check on Remove spaces between words to one

Note: check on other options (reading the descriptions) to use more functionalities

Step 15

Click on the Remove button

Step 16

You can see the spaces are removed and the data is cleaned

This is how you can use the “Power Tools” add-on to easily remove whitespaces and can use many more features.

Notes

Note that Extensions do not work offline.
There are more methods you can use to remove spaces, but I covered the quickest and most convenient methods
Other methods are Find & Replace, Substitute Function, and some more.
The trim function can take only one argument
The trim function cannot remove the line breaks
We use ARRAYFORMULA to enable the TRIM function taking cell ranges
We use CLEAN Function to enable the TRIM function to remove Line breaks

FAQs

How can I remove white spaces from the entire column?

You can simply use the TRIM function along with ARRAYFORMULA to remove whitespaces from a long range of cells can be one column or multiple columns as well. You can refer to it in the above sections.

How can I remove line breaks?

A function called CLEAN can be used to remove line spaces, but to work on the range and not a single cell, you can use ARRAYFORMULA and TRIM to make a combination of a powerful formula that will remove whitespaces from leading, trailing, and in between words. It will also remove the line breaks within a cell.

What if I want to remove specific spaces?

You can use the built-in tool by going to Data > Data Clean-up > Clean-up Suggestions. Here you can see where the whitespaces are detected in your data you can ignore the one which you have added intentionally and remove all the remaining ones.

Conclusion

Wrapping up how to remove spaces in google sheets. It was a pretty long tutorial but an easy one, I guess. I covered the three most convenient and most 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 have seen in google sheets. Then we learned how to remove space in google sheets using a TRIM formula, then we saw its limitations, and added ARRAYFORMULA with it, then further we added CLEAN formula to enable it to remove line breaks as well. This is how we made a complete formula that can clean the data in every way. Then we learned to install an add-on called “Power tools”, it’s a great add-on application that has more than handy features for day-to-day productivity in google sheets.

I hope you guys like this article and find it useful. For any problem, you can comment below. Please like and subscribe to our Blog and share the article with your social friends. Take care, and Keep learning with Office Demy.