In order to remove leading spaces in Excel, we can use Excel TRIM function. However, TRIM in Excel does not remove trailing spaces in Excel. The formula is a combination of TRIM, CLEAN, SUBSTITUTE & CHAR functions. In cell B2, the formula is =TRIM (CLEAN (SUBSTITUTE (A2,CHAR (160),' '))) In this way, Excel remove spaces which are not required. These trailing spaces cause the VLOOKUP to not classify the cells as a match. Although you can tell that ' BG33-8 ' is the same as ' BG33-9', Excel cannot. Column A has trailing spaces. You can use the TRIM function to remove leading and trailing spaces from a value.
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:
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:
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:
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.
How To Delete Trailing Spaces In Excel
This will be noticeably shorter as expected (10 characters in this example).
Non-Breaking Spaces
How To Delete Trailing Spaces In Excel Cell
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:
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 A2appears 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:
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.
Problem: None of my VLOOKUP formulas are working. I can clearly see that there is a match in the lookup table, but Excel cannot see it.
- None of the VLOOKUP functions work.
Strategy: A common problem is that either the item in column A or Column L has trailing spaces. This can happen if you downloaded the data from another system.
To fix this problem, you select cell A2 and press the F2 key to put the cell in Edit mode. A flashing insertion cursor will appear at the end of the cell. Check to see if the insertion cursor appears immediately after the last character or a few spaces away.
Edit cell L2 to see if there are trailing spaces. You will likely find that either column has trailing spaces. Below, you can see that there are a couple trailing spaces after the Item in column A. These trailing spaces cause the VLOOKUP to not classify the cells as a match. Although you can tell that 'BG33-8 ' is the same as 'BG33-9', Excel cannot.
- Column A has trailing spaces.
You can use the TRIM function to remove leading and trailing spaces from a value. If there are spaces between words, it will change consecutive spaces to a single space. For example, =TRIM(' Bill Jelen ') would change the cell contents to 'Bill Jelen'.
Additional Details: If the trailing spaces appear in your lookup value, use TRIM around that one value. Change =VLOOKUP(A2,$L$3:$M$30,2,FALSE) to =VLOOKUP(TRIM(A2),$L$3:$M$30,2,FALSE).
If the trailing spaces appear in the lookup table, then you can actually TRIM the entire table with one bizarre modification. Change the formula above to =VLOOKUP(A2,TRIM($L$3:$M$30),2,FALSE). But, don't press Enter after making the edit. Instead, hold down Ctrl and Shift and then press Enter.
Gotcha: That formula where you TRIM the entire lookup table is going to be insanely slow. It is fine for impressing your friends who use Excel, but in real life, it would be better to add a temporary column to TRIM each individual cell in column L. Then, copy that column and paste as values over column L.
Alternate Strategy: The other common problem of VLOOKUPs failing is numbers stored as text being used to look up a table with numeric values. Select column A and do Alt+DEF. Repeat with column L. Alt+DEF does a text to columns and converts text numbers to real numbers.