Skip to Content

How to Remove Trailing and Leading Spaces in Google Sheets

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.

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

Trim Whitespace-1

2. In the top menu select Data

Trim Whitespace-2

3. In the drop-down select Trim whitespace

Trim Whitespace-3

4. You should see a popup that tells you that whitespace was trimmed for a number of cells. Select the OK button

Trim Whitespace-4

5. All leading, trailing, and double spaces should now be removed from the range of cells that you highlighted in step 1

Trim Whitespace-5

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:

=TRIM(text)

  • 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:

TRIM function

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

Find and Replace Spaces-1

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

Find and Replace Spaces-3

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.

Find and Replace Spaces-4

5. Select Replace all

Find and Replace Spaces-5

6. Select Done

Find and Replace Spaces-6

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.

Closing Thoughts

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.

More Google Sheets Tutorials:
How to Insert Special Characters
How to Remove the Last Character
How to Replace a Space with a Dash