Remove Extra, Trailing, and Leading Spaces- Excel & Google Sheets
Written by
Reviewed by
Download the example workbook
This article demonstrates how to remove extra, trailing, and leading spaces in Excel and Google Sheets.
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(B3)
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(CLEAN(B3))
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(B3)
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.
=TRIM(C3)
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.
