How To Remove Spaces In Google Sheets

Google Sheets is a powerful and free spreadsheet tool. Many individuals, organizations, and businesses have found Google Sheets to be an invaluable addition to their collection of productivity tools. While it may not be quite as powerful as paid programs like Excel, Sheets offers an extensive range of functionality with a smooth learning curve.

Speaking of features, this article will show you how to use Google Sheets’ find and replace tool and a handy add-on to erase empty spaces from cells in your spreadsheet.

How to Remove Spaces in Google Sheets

There are several ways to remove spaces from a Google Sheets spreadsheet, including built-in features as well as a helpful add-on. Each of these methods is effective, but some are better suited for certain situations. Take a look at your options below and choose the one that best suits your needs.

The TRIM Function

If you have cells or columns full of text entries and need to get rid of any leading and trailing spaces, you can use the TRIM function.

TRIM allows you to remove leading and trailing spaces from cells, along with any extra spaces in the text. For this example, open a new Google spreadsheet and input the value ‘   455   643  ‘ in cell B3 with three leading spaces, two trailing spaces, and three spaces between the numbers.

Next, select cell B4 and click in the fx bar, then enter the function =TRIM(B3) in the fx bar and press Enter. Cell B4 will now include the same values as your original cell B3 with just one space between the numbers, as shown in the image above. ‘   455   643  ‘ becomes ‘455 643’ with the leading, trailing, and extra spaces removed.

The SUBSTITUTE Function

Google Sheets also has a SUBSTITUTE function that replaces text in cells. This feature enables users to modify cell content, and you can also use it to erase all cell spacing with the function.

The syntax for SUBSTITUTE is: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]). It’s like a find and replace function that searches for text in a cell and replaces it with something else.

To configure this function to remove all spacing from a text string, click cell B5. Next, enter =SUBSTITUTE(B3, " ", "") in the function bar and press Enter. Now B5 will return the number 455643 without any spacing in the text string as shown directly below.

If you need to copy that function to remove spacing from multiple cells, left-click the bottom-right corner of the cell that includes the SUBSTITUTE function and hold the button down. Then drag the cursor over the cells you need to copy the function to. A blue rectangle highlights the cells you’ve selected to copy the function to as shown in the example below.

Google Sheets Find and Replace Tool

You may not want to add a bunch of formulas to your spreadsheet or have rows of extraneous data clogging up your display. If you want to just remove the spaces from the existing text, Google Sheets has a find and replace tool with which you can find and replace text.

This gives you a quick way to find and replace text in multiple cells. As such, the tool enables you to remove spacing from cells without adding additional functions to the spreadsheet. You can open the tool by selecting Edit and Find and replace from the menu.

As an example, select cell B3. Then press the Ctrl + H hotkey to open the Find and replace dialogue box shown in the example below. The pop-up window includes text boxes where you would usually enter some text or numbers to find and some text or numbers to replace them with. But in this case, your goal is to remove extra spacing, so click in the Find box and enter one space using your space bar.

Next, press the Replace all button in the dialogue box, then click Done. This will remove all the spaces from cell B3. The text will also align to the right of the cell as Google Sheets thinks that the cell contains a number, and numbers right-align by default. As such, you might need to readjust the alignment as needed.

Alternatively, you can remove excess spacing without erasing all the spaces. Click the Undo button to restore the original spacing in cell B3, then select cell B3 again. Press Ctrl + H, input a double space in the Find box, click Replace all, then click Done. This process reduces all trailing and leading spacing to one space and cuts the spacing between text to one space only.

Remove Spaces with the Power Tools Add-on

Google Sheets also has various add-ons that extend its options and tools. Power Tools is an add-on for Sheets with which you can remove spaces and delimiters from cells. Press the + Free button on the Google Sheets add-ons page to add Power Tools to Sheets.

Once you have added Power Tools to Google Sheets, select a cell on your spreadsheet to remove spaces from. Select Add-ons from the pull-down menu then Power Tools. Then select Remove to open the Power Tools sidebar shown in the snapshot below.

Select Remove to open the remove spaces options shown below.

There are a number of options for clearing out spaces and other miscellaneous characters:

• Remove all spaces removes all spacing from the cell
• Remove leading and trailing spaces only removes the leading and trailing spaces
• Remove spaces between words to one will retain the leading and trailing spaces but erase any extra spacing between words
• Remove html entities removes any HTML tags
• Remove all delimiters removes delimiters used to separate fields such as commas used in Comma Separated (CSV) files or tabs used in tab-delimited files

This feature’s a great way to clean up a Google Docs sheet to make it more useable, stripping out spacing and characters that might interfere with the data or text. For example, you may want to upload a list containing a number of fields in an Email Service Provider (ESP) and you need to clean up up the file before exporting it back to a CSV file to successfully upload to your ESP account.

Final Thoughts

So there are two functions and a built-in find and replace tool that you can use to remove extra spaces Google Sheets, as well as an add-on that includes this feature along with other useful tools for Google Sheets.

You might also be interested in How to combine cells in Google Sheets.

Have any Google Sheets tips and tricks? Please share them with us in the comments below!