This post discusses one way to enable our lookup functions, such as VLOOKUP, to work even when the lookup values contain extra spaces. We’ll use the TRIM function to dynamically remove padding from the lookup values.
Overview
- Click in the first cell of the Trim column. On the Formulas tab, click the Text dropdown menu in the Functions group and select TRIM. After the dialog box appears, click on the cell that contains the text you would like to remove spaces from to complete the function.
- Using the Trim function, you have learned how to remove trailing spaces in Excel and leading spaces too. There can be non-breaking spaces in a text as well. To remove them, simply use Substitute Function and replace the non-breaking space with a normal space.
Select the range you want to remove all trailing spaces from cells, then click Kutools Text Remove Spaces. In the Remove Spaces dialog box, check the Trailing Spaces option in Spaces Type section and then click the OK button. For example, if you want to remove trailing spaces, you can create a new column to clean the data by using a formula, filling down the new column, converting that new column's formulas to values, and then removing the original column. The basic steps for cleaning data are as follows: Import the data from an external data source.
We’ll use a specific example to demonstrate this function. Let’s say we’ve exported a partial income statement from our accounting system. For some technical reason, it appears that all report labels are padded with trailing spaces so that the total label length is 40 characters. As we inspect each report label, we observe that there are a different number of trailing spaces for each label, as shown below:
These extra trailing spaces create problems when we try to perform lookups. Here’s why.
The accounts in the budget table do not have trailing spaces. The budget table (tbl_bud) is shown below:
When we write a formula using a lookup function such as VLOOKUP, the lookup value is not found in the lookup range. This is because the lookup value contains the trailing spaces, and the accounts stored in the lookup range do not. To Excel’s lookup functions, these are two different values.
The formulas thus produce errors, as demonstrated below:
In order to ensure that the lookup value can be found in the lookup range, we need to remove the trailing spaces. Although we could do this manually, we like to delegate manual tasks to Excel, especially in our recurring use workbooks. So, we enlist the help of the TRIM function. The TRIM function removes any extra spaces, including leading spaces, trailing spaces, and double spaces. The TRIM function has a single argument, the text to trim.
By wrapping a TRIM function around the lookup value, we remove the extra spaces and enable the VLOOKUP function to make its match.
We could use the following formula to perform the lookup:
Where:
- TRIM(B3) is the lookup value, stripped of extra spaces
- tbl_bud is the lookup range, the budget table
- 2 the column that has the value to return, the amount column
- 0 means exact match
When we use this formula, the budget values are properly returned to our report, as shown below:
The TRIM function removes the extra spaces and enables the VLOOKUP function to make its match.
Delete Trailing Spaces In Excel
If you have other practical uses for the TRIM function we’d love to hear more…please post a comment below.
Additional Resources
- Download sample file: Trim
- For other blog posts that reference VLOOKUP
- To handle trailing spaces in the lookup range (instead of the lookup value), check out the wildcard post
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.
Remove Trailing Spaces In Excel Column
- 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.