Remove Extra Spaces from Lookup Values with TRIM
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.
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:
- 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.
If you have other practical uses for the TRIM function we’d love to hear more…please post a comment below.
- 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