As you working with spreadsheet programs like Google Sheets, you will likely eventually find unwanted extra spaces in your cells.
This could be leading spaces that occur at the beginning of a cell, trailing spaces that are at the end of a cell, or extra spaces somewhere between the text or numbers in the cells.
This unwanted white space will make your spreadsheet data look uneven, so it is definitely something you should try to fix.
In this tutorial, I will show you how to remove all the unwanted spaces in Google Sheets.
Table of Contents
Remove Spaces with Trim Whitespace
Google Sheets has a built-in option that will remove leading spaces, trailing spaces, and double spaces. In Google Sheets, this is called “Trim whitespace”. This is the easiest way to remove unwanted spaces in your spreadsheet.
Here is how this is done:
1. Highlight the range of data that you want to remove unwanted spaces for
2. In the top menu select Data
3. In the drop-down select Trim whitespace
4. You should see a popup that tells you that whitespace was trimmed for a number of cells. Select the OK button
5. All leading, trailing, and double spaces should now be removed from the range of cells that you highlighted in step 1
Remove Spaces with TRIM Function
There is also a TRIM function that will remove spaces from text. The trim function will remove leading, trailing, and double spaces in text.
The syntax of the TRIM function is:
- text – the string or cell reference containing the string that will have the extra spaces removed from
Here is an example of how this can be used in a spreadsheet:
The good thing about using the TRIM function over the Trim whitespace tool is that the function can be combined with other formulas.
Remove Spaces with Find and Replace
Another option to remove spaces in your spreadsheet is to use find and replace. This is good for removing double spaces or all spaces in your cells.
Here is how this is done:
1. Highlight the range of cells that you want to remove spaces for
2. On your keyboard press the CTRL + H keys (Command + H if using a Mac). This will open Find and replace
3. In the Find field, enter the spaces that you want to remove. If you want to remove double spaces enter two spaces here. If you want to remove all spaces enter a single space here
4. In the Replace with field we need to enter what we want to Replace from the Find section. If you are replacing double spaces with single spaces, enter a single space here. If you are removing all spaces leave this field blank.
5. Select Replace all
6. Select Done
You should now see that your spaces have been removed.
Again the Find and replace method can only remove double spaces or all spaces. You can’t target trailing or leading spaces without a space in the middle of your data.
That is it for this tutorial. We have now shared three methods for removing unwanted spaces from your cells in Google Sheets.
Each method has its pros and cons.
The Trim whitespace option is the easiest, but it can’t remove single spaces in the middle of cells.
The TRIM function is useful because you can combine it with other functions but it’s a little more tedious than using the Trim whitespace option.
The find and replace method is useful because you can remove all spaces if you want. However, you can’t specifically target trailing and leading spaces without removing all of the spaces in a cell.
We recommend you experiment with each method so that you know which one to use the next time you are dealing with annoying spaces in your spreadsheet.