How to remove leading and trailing spaces in an entire column in Excel & Google Docs
Posted by: AJ Welch
Nearly every computer user in this modern age has spent his or her fair share of time tinkering around with Excel, trying to coax those silly little cells into behaving just as we need them to, but often they never turn out quite as we expect.
Today we’ll examine a few simple techniques to ensure that all your text data in Excel (or Google Docs) is clean and tidy by eliminating any and all leading or trailing spaces.
The Trim function
The most obvious (and generally efficient) method for removing both leading and trailing space is to use the TRIM() function. As stated in the official documentation, TRIM() “removes all spaces from text except between words.” Unfortunately, in the case of both Excel and Google Docs, that simply isn’t always true, as we can easily illustrate with a bit of code.
In a new sheet, begin by inserting a single word into the first cell of column A, or cell A1, paying special attention to include a few spaces before and after the word. My example of A1 contains:
=" The Hobbit "Column B, will be our first test column, and the simplest test for our purposes is to check the length of the text in our cells in column A, using the lovely LEN() function.
To display the length of cell A1 (which in my example text is 18 characters long), the contents of cell B1 should look like this:
=LEN(A1)Nothing fancy, but this will display the total length of our cell from column A.
Now we’ll try out TRIM(), so modify cell C1 like so:
=TRIM(A1)The text will be obviously trimmed to the naked eye, but just to verify, modify cell D1 this time to check the length of our TRIMMED value.
=LEN(A3)This will be noticeably shorter as expected (10 characters in this example).
Non-breaking spaces
As it happens, often when importing text into Excel or using copy/paste, we’re inadvertently adding non-breaking space characters to the text, which are not properly handled as expected by the TRIM() function.
To illustrate, add a new row to our test sheet by modifying cell A2:
=CHAR(160) & CHAR(160) & CHAR(160) & "The Hobbit" & CHAR(160) & CHAR(160) & CHAR(160) & CHAR(160) & CHAR(160)
In Excel, the CHAR() function allows us to output characters based on the numeric identifier of that character. In this case for testing, CHAR(160) is a non-breaking space, so the contents of A2 appears to match that of A1, but in fact the spaces on either side for A2 are non-breaking.
Now copy/paste the three test cells B1:D1 and paste them into row 2 starting at B2. We’ll now clearly see the problem with TRIM() – while it worked in row 1 and removed all the normal spaces, it doesn’t do anything in row 2 and thus our ‘TRIMED’ version is still the full 18-length string.
Absolutely removing the excess
To fully resolve our issue and ensure we always remove leading and trailing spaces of all kinds, copy all four cells in row 2 down into row 3, so rows 2 and 3 are presently identical.
The trick is to combine TRIM() with a few more functions.
We start by using SUBSTITUTE() to replace all instances of CHAR(160) (non-breaking space) with normal space characters.
We also use the CLEAN() function which attempts to remove characters that simply wouldn’t display on your machine in the first place.
The end result is the following code, which should replace what you have in cell C3:
=TRIM(CLEAN(SUBSTITUTE(A3,CHAR(160)," ")))Voila! We’re now able to remove all leading and trailing spaces in Excel (and Google Docs) no matter what type of space it is. To apply this to other cells in the column, just copy and paste it, changing the cell specification above from A3 to whatever is necessary to get started.