Fixing downloaded data

I’ve imported data into Excel and I’ve tried to do a VLOOKUP on the data but it doesn’t return any results. What could the problem be?

By Neale Blackwood

It is common when importing into Excel that the data has leading or trailing spaces. These additional spaces can stop your VLOOKUP from returning the result you were expecting. Leading spaces are easy to see because the text is not left aligned. Trailing spaces are harder to identify because when you look at the data it appears correct.

You usually become aware of trailing spaces when you edit a cell and see the cursor a few spaces to the right of the characters in the cell. The TRIM function will remove leading and trailing spaces. Any spaces within the text are unaffected. If A1 contained the text “test data ” the formula =TRIM(A1) would return “test data”.

To remove leading and trailing spaces in a data column you would use another empty column and insert a TRIM function referring to the first cell in the data column and then copy it down as far as the data.  Then copy the column with all the TRIM functions and Paste Special > Values on top of the data column to remove the leading and trailing spaces. Your VLOOKUP should then work.

If that doesn’t work then the problem may be that the data is formatted as text. For example, numbers are left aligned instead of right aligned. If that is the case, you can insert an apostrophe (‘) to the right of the code you are using as your lookup value. This will treat it as text and match the data.

This should allow your VLOOKUP to work!