How To Remove Spaces in Google Sheets
Google Sheets is a powerful and free spreadsheet tool offered as part of the Google Docs web service. Many individuals, organizations and businesses have found Google Sheets to be an invaluable addition to their computing lives, since the spreadsheet (while not having quite the reach and power of paid programs like Excel) offers an extensive range of functionality with a smooth learning curve. And Google Sheets is free.
Although Google Sheets might not have as many spreadsheet features as Excel, it’s still an excellent cloud software alternative. In the essential functions of a spreadsheet, it pretty much matches Excel feature for feature.
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.
The TRIM Function
If you have cells (or more likely columns) full of text entries and need to get rid of any leading and trailing spaces, you can use the TRIM function, which operates just like the similar function included with Excel.
TRIM enables 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 and two trailing spaces plus three more 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. Then cell B4 will include the same values as your original cell B3 with just one space between the numbers as shown below. ‘ 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. That 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 Sheet’s 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 Find and Replace 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, removing all the spacing from cell B3. The text will also align to the right of the cell (because now Google Sheets thinks that the cell contains a number, and numbers right-align by default) so 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 to 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.
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 (Power Tools) 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!