# 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.

## Introducing Visual SQL

SQL may be the language of data, but not everyone can understand it. With our visual version of SQL, now anyone at your company can query data from almost any source—no coding required.