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.

Overview

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:

20140227-a

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:

20140227b

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:

20140227-d

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:

=VLOOKUP(TRIM(B3),tbl_bud,2,0)

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:

20140227-e

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.

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

 

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

8 Comments

  1. Ashley on June 17, 2015 at 12:42 am

    What if there could be spaces in both the lookup value and the array? Using trim and wildcard together don’t work, for example:

    =VLOOKUP(TRIM(A1)&”?”,C:C,1,0)

    I’m actually working with an INDEX MATCH function but the same principle applies. TRIM and wildcard functions on their own don’t work either. Also tried adding TRIM and wildcard to the lookup array which just doesn’t work.

    Any suggestions?

    • Kurt LeBlanc on July 1, 2016 at 5:53 am

      Ashley,

      I recommend looking into the Power Query capabilities of Excel to handle cleaning the table quickly before writing your formula.

      Thanks,
      Kurt

  2. Vijay on July 20, 2015 at 3:03 am

    I tried using trim it works perfectly, but the problem is it just edits the spaces in lookup value but not in lookup range. Is there any formula which is used to edit the spaces in lookup range and show the result?

    • Kurt LeBlanc on July 7, 2016 at 5:37 am

      Hey Vijay,

      You can use the Power Query to edit a table and just refresh the Query when you add data! It is an awesome tool in Excel:)

      Let me know if you need more help
      Kurt LeBlanc

  3. Maria on October 3, 2016 at 8:21 am

    I need help with this one please
    Colum D has 2 spaces after the value and when I enter the vlookup formula it gives me n/a
    Here’s the formula: =VLOOKUP(AF178,RemHD!D:U,17,FALSE)

    • Kurt LeBlanc on October 4, 2016 at 12:51 pm

      Hey Maria

      I’m glad to help:)

      Excel’s Help Guide suggests that the column in the table you are matching the value to is not the left-most column in the table. That is the major problem with VLOOKUP. My preference is the INDEX/MATCH combo as explained in the blog https://www.excel-university.com/two-dimensional-vlookup/

      Also, you will still have an “error” in that the function will not find any matching values for you. The values need to match exactly so the lookup value needs to have the same characters as the table’s values so there’s a couple ways to do this:

      First, you can use the Power Query Feature to trim the table’s data in one click. It creates a new table and is ONLY one click more than necessary but will produce the same result as, my personal preference:

      In the function, you can join the asterisk (*) to the end of the lookup value to become the lookup value followed by any characters

      =VLOOKUP(AF178&”*”,…

      Both techniques work, so it is up to you which you like:)

      Let me know how that works for you!
      Kurt LeBlanc

  4. Ines Oswaldo Cruz on January 21, 2018 at 2:24 pm

    The TRIM work! Thanks!

  5. Anil on September 11, 2019 at 2:16 am

    I’m trying to use Trim function in Index&match as there is text in my table array after applying formula i’m getting #N/A error,please help with correct way of writing formula

    =INDEX(‘Icl data’!$A$1:$D$15558,MATCH(‘Dhl data’!$A2,TRIM(‘Icl data’!$A$1:$A$15558),0),MATCH(‘Dhl data’!B$1,TRIM(‘Icl data’!$A$1:$D$1),0))

Leave a Comment