Remove Extra, Trailing, and Leading Spaces- Excel & Google Sheets

This article demonstrates how to remove extra, trailing, and leading spaces in Excel and Google Sheets.

remove extra trailing leading spaces Main Function

Removing Extra Spaces

To remove extra spaces in a cell, we will use the TRIM Function. The TRIM Function removes trailing spaces, extra spaces between words, and leading spaces.

TRIM

Removing extra spaces in a cell helps avoid errors when working with formulas. An example of a formula that could result in an error is the VLOOKUP Function.

The TRIM Function in the example above removed all extra spaces in the cells and returned a clean data.

Removing Line Spaces

When working in Excel, we sometimes import data from other sources that has extra spaces and line breaks. The TRIM Function alone cannot take care of the line breaks.

In other to remove line breaks and spaces, we will use a combination of the TRIM and CLEAN Functions.

Trim and Clean

The formula above removes both the line breaks and the extra spaces in each cell.

First, the CLEAN Function removes the line breaks in the cells, and the extra spaces left is then removed by the TRIM Function.

The CLEAN Function

The CLEAN Function removes the line breaks in the specified cell.

Clean

From the example above we see that the CLEAN Function first removes the lines breaks in the cells, with only the extra spaces remaining.

We will then use the TRIM Function on the cells to remove the extra spaces left.

Clean Then Trim

Combining both gives us a clean data that is easy to work with.

Remove Extra Trailing and Leading Spaces in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

remove extra trailing leading spaces Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!