How to Remove Trailing and Leading Spaces in Google Sheets

There are several methods available that allow you to remove trailing and leading spaces in Google Sheets.

Leading spaces are unnecessary spaces that appear at the beginning of the cell. Trailing spaces occur at the end of the cell. 

Let’s look at a scenario where we might have to remove these unnecessary spaces.

You have a Google Form that asks respondents to provide their cellphone number. Since this is user input, it’s possible for there to be inconsistencies in our answers. Looking at the responses, you notice that some answers have trailing spaces, leading spaces, or both.

Google Sheets can make your data more uniform by removing these spaces. There is a built-in “Trim Whitespace” feature that can be accessed from the Data Menu. If you prefer to use a Google Sheets formula for data cleaning, Google Sheets also has a TRIM function for removing unneeded spaces.

Trimming these trailing and leading spaces is very useful when cleaning your dataset. Besides numerical data, we can also apply this to other forms of data, such as addresses and names.

Now that we know when to remove trailing and leading spaces in Google Sheets, let’s look at how we can do this in practice.

How To Use the Built-In Trim Whitespace feature

Let’s look at a quick example of using the built-in Trim Whitespace function.

In the example below, the first column has several values which have some combination of trailing and leading spaces. Using the LEN function, we can measure the actual number of characters on each string.

The result of the Trim Whitespace feature can be seen in the third column. When we measure the lengths again, they are much shorter and correspond to the actual number of digits in the string.

Here’s how we can use the built-in Trim Whitespace option:

1. First, select the cell or cell range that we want to trim. In this example, we’ll select the cell range A11:A14.
   
   
2. We can copy and paste this range to another column labeled “Trimmed Text”. This is an optional step if you wish to preserve the original values.
   
   
3. Next, find the Data menu and look for the option labeled Data cleanup. Select the Trim whitespace option.
   
   
4. Google Sheets will automatically remove all trailing and leading spaces in the selected cell range.
   
   

Using the TRIM Function in Google Sheets

Besides the Trim Whitespace option, we can also use the TRIM function in Google Sheets.

Column C in the example below uses the TRIM function with the text values in Column A as arguments. Using the LEN function, we can confirm that the lengths of each string have shortened.

To get the values in Column C, we just need to use the following formula:

=TRIM(A2)

One advantage of using the TRIM function is that it’s dynamic. If we add a new input to cell A2, cell C2 will automatically update after trimming out any trailing or leading spaces in the new value. 

We can also use the TRIM function with other Google Sheets functions in the same formula. For example, we can combine the LOWER and TRIM functions to create an output seen below.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out the TRIM function in Google Sheets, keep reading to find out how you can use it yourself.

How to Use TRIM function in Google Sheets

This section will guide you through each step needed to start using the TRIM function in Google Sheets. You’ll learn how to use this function to remove trailing and leading spaces in Google Sheets.

1. First, we must identify which text values to trim. In the example below, column A has multiple values with trailing and leading spaces. We can start by typing the trimmed text of cell A2 in the corresponding cell in the third column.
   
   
2. Next, type the equal sign ‘=‘ to begin the function, followed by ‘TRIM(‘. 
3. We can click on the upwards arrow on the top-right-hand corner of the box to minimize the tooltip popup.
   
   
4. The TRIM function requires only one argument, the value to trim. The function also accepts cell references that contain the value needed. In this example, we’ll add the cell reference A2 to our function.
   
   
5. Hit the Enter key on your keyboard to return the trimmed text.
   
   
6. We can drag the formula down to fill out the rest of the column.
   
   

Frequently Asked Questions (FAQ)

1. Why do I need to remove trailing and leading spaces in my data?
   If you’re performing data analysis, trailing and leading spaces can affect your findings. If you’re using the spreadsheet as a source for an output like an email template, it may make the final result look unclean or inconsistent.
2. Can I use the TRIM function to remove spaces between words in a string?
   No. The function only removes trailing and leading spaces. If you want to remove all spaces, then you might want to use the SUBSTITUTE function instead. 

That’s all you need to remember to start removing trailing and leading spaces in Google Sheets. This feature is useful when you want to clean your data or if you want a uniform format for your data. We can use either the built-in Trim Whitespace option or the TRIM function. The latter is more powerful and can be used with other functions.

With so many other Google Sheets functions out there, you can certainly find a function that can help with your use case.

Are you interested in learning more about what Google Sheets can do? Stay notified of new guides like this by subscribing to our newsletter!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe