Find space from right in Excel cell

A common difficulty with spreadsheet users is how to find a space from the right in an Excel cell. These spaces often result in VLOOKUP’s and other functions not working correctly, because in Excel terms, a space at the right of a cell is completely different from a cell with no space at the end.

Finding the spaces

As shown below, the issue is particularly relevant for spaces at the end of the cell. If a space is at the beginning it is obvious to the eye (as long as there is not too much data). But can you tell which cells have a space on the right hand side? You will either need to click into each cell to see the space or use the trick below.

Find space from right in Excel cell

Extract space from right in Excel cell

By using the RIGHT function, you can easily create a helper column and extract the last character on the right from a cell.

As shown below in Column B, we have used the RIGHT function to pull 1 character from the right hand side of each cell, effectively the last character in the cell. It is easy now to see which rows have an extra space at the end, and, by using a filter you could identify all the cells that have this problem.

If you want to be more explicit, column C uses an IF function based on column B to differentiate between cells that end in a visible character versus a space.

Find space from right in Excel cell

Keep in mind that the above just looks at the last character to see if it is a space. There could be more than one space which is just as difficult to see just to the human eye.

Fix the extra space/s at the end of cells

If you don’t really care where the end of cell extra spaces are, you just want to fix them, you can use the TRIM function (in some cases you can use FIND/ REPLACE but only if you don’t want any spaces- some spaces in the middle of the cell may be acceptable).

As shown below, using a simple TRIM function in Column B, not only are all the end of cell spaces removed, but also the space in A8 which was at the beginning of the cell.

Find space from right in Excel cell

Related

Removing spaces from cells, even when it isn’t a space

Vlookup not finding a value that is there (when using Find)

Excel not recognizing spaces

Remove characters in excel cells