• Excel Book Excerpt

Excel Remove Leading and Trailing Spaces

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Remove Leading and Trailing Spaces

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.

LE10000428.jpg

Figure 427 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.

LE10000429.jpg

Figure 428 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. If A2 contains text and needs to be a number, use =VLOOKUP(VALUE(A2),. If A2 contains a number and needs to be text, use =VLOOKUP(TEXT(A2,"0"),.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: