Remove Extra Spaces from Lookup Values: VLOOKUP with TRIM

While using VLOOKUP formula, if the data consists extra spaces , we may not get the results. VLOOKUP checks for exact match and even single extra space in data also will not give the results and will show #N/A error. But VLOOKUP is not case sensitive. In this article we will explain how to use VLOOKUP with TRIM to remove extra spaces in following situations:

  • How to use VLOOKUP with TRIM when the lookup_value consists of extra space.
  • How to  use VLOOKUP with TRIM when the table_array consists of extra space.

To remove extra spaces in the data, we can use TRIM function. TRIM function removes the extra space from the text. It considers one space between the text as normal. We can use TRIM with VLOOKUP in as below.

How to use VLOOKUP with TRIM to remove extra spaces from lookup_value .

When we put VLOOKUP where the lookup_value consists of extra space, we can apply TRIM to lookup_value. In this example, Employee Name contains extra spaces. If we put VLOOKUP, it will not match with name in table and it will show #N/A error. We can put TRIM to lookup_value as below to match the data in table.

Remove Extra Spaces
=VLOOKUP(TRIM(F6),C:D,2,0)
vlookup with trim

TRIM will remove space in lookup_value and match in table. Since employee name matches in table it will return the expected results.

How to  use VLOOKUP with TRIM when the table_array consists of extra space.

In the above example, the lookup_value consists of extra space. But in case if the data table values consists of extra spaces, it is very difficult to clean up the data table. In that case we can put VLOOKUP array formula as below:

remove extra space

You can put TRIM function to table_array when there are extra space in the table. Since this is an array formula, we have to put Ctrl+Shift+Enter . The disadvantage of this formula is the output will be in text format even if it is a number. 

{=VLOOKUP(F11,TRIM(C:D),2,0)}

In case if the extra space added only before and after the text (Not between the words), we can use wildcard “*” to lookup_value

=VLOOKUP("*"&F12&"*",C:D,2,0)

You can read more about wild card with Vlookup in this post: https://excelhub.org/vlookup-wildcard-search-in-excel/

Leave a Reply

Your email address will not be published. Required fields are marked *