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
=" The Hobbit "
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
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:
Nothing fancy, but this will display the total length of our cell from column
Now we’ll try out
TRIM(), so modify cell
C1 like so:
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
This will be noticeably shorter as expected (10 characters in this example).
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
To illustrate, add a new row to our test sheet by modifying cell
=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
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
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.