Download the example workbook
This article demonstrates how to remove extra, trailing, and leading spaces in Excel and Google Sheets.
Removing Extra Spaces
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.
The formula above removes both the line breaks and the extra spaces in each cell.
The CLEAN Function
The CLEAN Function removes the line breaks in the specified cell.
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.
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.