Using VLOOKUP – How to Avoid #N/A by Getting Rid of Spaces in the Backend List

VLOOKUP is pure bites, it doesn’t know if a guy mistypes a text. When Using VLOOKUP, “Martha” is not the same as “Martha ”

What does this mean? When Using VLOOKUP, the matching must be absolute. That is, letter by letter (not case sensitive), space by space, character by character.

The fact is that Excel Lists usually comes from txt files that come from some external source (BI software, other Excel file, etc) and from time to time bring spaces.

Explore these 5 handy techniques to get rid of leading, trailing and additional inter-spaces (in the index column/leftmost column).

Using VLOOKUP will be better…

  • Export the txt file with the least amount of messy data. Why invest time and effort in post-importing-editing when you can configure the exporting process? If you are receiving a messy txt file each month, contact the guy who exports the data. It should be a way to avoid exporting unnecessary data or in the wrong format. If you cannot do it, then do one of the following things below…
  • Delete leading and trailing spaces with “Text to Columns” command (Delimited). Use this method for single values in the index column. Turn “Martha ” into “Martha” but not “Martha Wilson ” into and “Martha” and “Wilson”. Proceed this way:
    • Insert as many columns as you need to the right to avoid replacing existing data after the splitting (delete them after the editing process)
    • Select the index column to edit
    • Open Text to Columns command. Excel 2003 users go to Data>Text to Columns. Excel 2007 users go to Data>Data Tools>Text to Columns
    • Choose the original data type in step 1/3 as “Delimited”
    • Choose the delimiter in step 2/3 as “Space” (If you have additional inter-spaces you must use TRIM function)
    • Press Next and Finish
  • Delete regular leading and trailing spaces with “Text to Columns” command (Fixed Width). Do this when the spaces are regular and you don’t have inter-spaces. For example, you can split the following strings by a break line after A: “00016A ”,“00019A ”. Proceed this way:
    • Insert as many columns as you need to the right to avoid replacing existing data after the splitting (delete them after the editing process)
    • Select the index column to edit
    • Open Text to Columns command. Excel 2003 users go to Data>Text to Columns. Excel 2007 users go to Data>Data Tools>Text to Columns
    • Choose the original data type in step 1/3 as “Fixed Width”
    • Create the appropriate break line by clicking on the data preview in step 2/3 (If the spaces are irregular and especially if they have inter-spaces you must use TRIM function, see below…)
    • Press Next and Finish
  • Delete leading, trailing and inter-spaces with a Helper Column and TRIM function. TRIM will turn “Martha ” into “Martha” or “Martha Wilson ” into “Martha Wilson”. Proceed this way:
    • Add a column to the left/right of the messy column
    • Write the function TRIM. It will delete all leading, trailing and inter-spaces.
    • Copy and paste values in source column
    • Delete the Helper Column
  • Delete spaces by replacing them with nothing. Do this when you have an index column with one string value, for example: “Martha ”, “Susan ”. Proceed this way:
    • Press CTRL+H (Find and Replace command)
    • Type a space in the “Find what:” box
    • Leave the “Replace with:” box blank
    • Press Replace All

Apply these techniques before Using VLOOKUP. This way, you will avoid unnecessary frustration.

Just to finish, Excel Users face a lot of problems when Using VLOOKUP. Even if you get the skills to solve them all, you need other tools to get your Reports done.

Learn here what functions/formulas you need to become an Excel Data God!