How to Remove Spaces in Google Sheets [3 Methods]

how to Remove Spaces In Google Sheets 17

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.

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.

See also  How to Zoom Out on Google Sheets [5 Methods]

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

how to Remove Spaces In Google Sheets 1

Step 2

Select all the data

how to Remove Spaces In Google Sheets 2

Step 3

Go to Data > Data Clean-up

how to Remove Spaces In Google Sheets 3

Step 4

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

how to Remove Spaces In Google Sheets 4

how to Remove Spaces In Google Sheets 5

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.

how to Remove Spaces In Google Sheets 6

how to Remove Spaces In Google Sheets 7

Step 6

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

how to Remove Spaces In Google Sheets 8

Step 7

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

how to Remove Spaces In Google Sheets 9

how to Remove Spaces In Google Sheets 10

Step 8

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

how to Remove Spaces In Google Sheets 11

Step 9

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

how to Remove Spaces In Google Sheets 12

Step 10

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

how to Remove Spaces In Google Sheets 13

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 14

See also  How to Use IFNA Function in Google Sheets (User Guide)

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

how to Remove Spaces In Google Sheets 15

Step 2

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

how to Remove Spaces In Google Sheets 16

Step 3

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

how to Remove Spaces In Google Sheets 17

how to Remove Spaces In Google Sheets 18

Step 4

Hit Enter, and you’re done.

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

how to Remove Spaces In Google Sheets 19

how to Remove Spaces In Google Sheets 20

Step 5

Passing a range of cells

how to Remove Spaces In Google Sheets 21

Step 6

An error returned

how to Remove Spaces In Google Sheets 22

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

Step 7

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

how to Remove Spaces In Google Sheets 23

Step 8

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

how to Remove Spaces In Google Sheets 24

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

how to Remove Spaces In Google Sheets 25

Step 10

Now pass this cell in this function

how to Remove Spaces In Google Sheets 26

Step 11

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

how to Remove Spaces In Google Sheets 27

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

Step 12

Adding CLEAN function inside the TRIM function, inside the ARRAYFORMULA

how to Remove Spaces In Google Sheets 28

Step 13

Hit Enter key, and see the result

how to Remove Spaces In Google Sheets 29

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 30

See also  How to Make Categories in Google Sheets [Guide 2022]

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

how to Remove Spaces In Google Sheets 31

Step 2

Search “Power Tools” in the Google Workspace Marketplace

how to Remove Spaces In Google Sheets 32

Step 3

Click on the “Power Tools” app from the results.

how to Remove Spaces In Google Sheets 33

Step 4

Click on Install

how to Remove Spaces In Google Sheets 34

Step 5

Click on Continue

how to Remove Spaces In Google Sheets 35

Step 6

Choose a google Account to continue the installation

how to Remove Spaces In Google Sheets 36

Step 7

Allow the permission

how to Remove Spaces In Google Sheets 37

Step 8

Power Tools Successfully installed. Click on Done

how to Remove Spaces In Google Sheets 38

Step 9

Close the Tab

how to Remove Spaces In Google Sheets 39

Step 10

Go to Extensions > Power Tools > Tools > Text

how to Remove Spaces In Google Sheets 40

Step 11

Click on Remove

how to Remove Spaces In Google Sheets 41

Step 12

Click on Remove spaces and delimiters

how to Remove Spaces In Google Sheets 42

Step 13

Check on Remove leading and trailing spaces

how to Remove Spaces In Google Sheets 43

Step 14

Check on Remove spaces between words to one

how to Remove Spaces In Google Sheets 44

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

Step 15

Click on the Remove button

how to Remove Spaces In Google Sheets 45

Step 16

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

how to Remove Spaces In Google Sheets 46

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

See also  Calculate Average, Mean, Mode and Standard Deviation in Google Sheets

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

See also  How to Add/Create Drop Down List in Google Sheets

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.

See also  How to Insert Degree Symbol in Google Sheets [5 Methods]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general