Skip to content

Excel TRIM Function: Remove Trailing Spaces in Text

    TRIM & VLOOKUP

    Watch the video, follow the tutorial below and download the featured file.

    The TRIM function removes all spaces in a text string except for a single space between words.  A common use for the TRIM function is where you are using a VLOOKUP to join two sets of data.  If the two sets of data have been entered or imported with space character discrepancies then TRIM will sort this out for you.

    See the example below: I have a master list of product prices in the first table which I want to use in the second table.  The VLOOKUP will cross reference the product codes in table 2 with the product codes in table 1 and carry across the correct price.  Some of the product codes in table 2 have additional spaces between the text portion and number portion of the code as well as trailing spaces.

    vlookup example

    Additional spaces between characters in a text string are easy to spot but trailing spaces are more difficult to see. A quick way to identify a trailing space is to enter edit mode using the F2 key.  In edit mode you will be able to see if there is a space between the last character and the cursor –see below.

    trailing space

    If I perform a VLOOKUP without the TRIM function, I get a number of error #N/A results, meaning that no match can be found. These product codes have been entered with too many spaces.

    vlookup no match errors

    If I TRIM the lookup_value in the VLOOKUP function, the error goes away.

    VLOOKUP without TRIM  =VLOOKUP(D2,master_list,2,FALSE)

    VLOOKUP with TRIM = VLOOKUP(TRIM(D2),master_list,2,FALSE)

    using trim with vlookup

    Using TRIM on the lookup_value works if the lookup_value contains the offending spaces. What if values in the lookup table contain the offending spaces?  One of the ways of dealing with this would be to create a new column next to those values and use the TRIM function to strip out the spaces.  You could then copy and paste values in your new column and delete the original.

    Leave a Reply