It can be frustrating to comb through a dataset looking for where your formula went wrong only to discover there’s an extra space in the cell. If you’re trying to compare two lists, perform a lookup, or otherwise use the data, you won’t be able to use a cell that has an extra space in any scalable way.

How to Remove Spaces in Excel

In the workbook above, we’re comparing C2 with A2. While the employees look the same, we can see immediately they are NOT the same employee number. The culprit is a space in front of the “K” in each of the employee numbers in column K.*

*Wondering how to spot an extra space when its not so obvious? Scroll down for more on that

Using the Trim Function in Excel

To remove the extra space, the easiest way to do this is using the TRIM function. The trim function is designed to do one thing:

1. Remove any extra spaces at the beginning and end of the cell (if the cell contains multiple spaces, only the extra space(s) at the beginning and end will be removed
   1. For the more technical Excel users, this only removes CHAR(32) from the cell and NOT CHAR(160)

If there is another type of character at the beginning or end of your cell, you maybe want to consider the Substitute function.

IN B2, we’ve used the TRIM function on A2, which removed the extra space. When we compare the two cells now, they are equal. From here, we just copy & paste those new values into Column A. Voila! We have removed an extra space from a cell in excel

How to Identify an Extra Space in a Cell

While it is very easy to remove an extra space, how would you go about identifying that there is an extra space in first place? Especially if its hiding at the end of a cell?

Let’s revisit our first example (below). The extra space was at the front of the cell, making it easy to spot the problem we have to fix. But what if the space is at the end of the cell? They look identical, but after comparing them its clear they are not. Enter the LEN function.

The LEN Function

The LEN function simply measures the length of cell in total number of characters (including a space). We can immediately see the employee numbers in Column A have an extra character when compared to column C.

Using the TRIM function once again, we eliminate extra spaces from the cells in Column A. Once we copy/paste the values into column A, let’s check the length of the cells.

They are now equal.