Text to Column, Vlookup, External Data Problem

[ft9a]

My excel vlookup in sheet "NEW", in column D is not reading the lookup value in that sheet from coulmn A. I can fix it by making the column array from "Sheet1", column A by using text to column, delimited, next, check space, then finish, but it asks me "Do you want to replace the contents of the destination cells?", if I click ok, it deletes column b,c,d in "Sheet1". Sheet 1 is pulled from an access query. How can I get the vlookup to work? File attached.

[AB33]

You need to insert new columns first before you do text to column since you are splitting a single cell delimited by a separator in to many cells(columns)

[AlKey]

Hi ft9a,

Please see attached file. There were trailing spaces that I had do remove in Column A "NEW" for the VLOOKUP to work.

[Xx7]

As noted.. there are trailing spaces. Use the TRIM() function to remove these

[ft9a]

Can you give me a step by step on how you got rid of the trailing spaces in column A, I have a few to do on my own. Thank You

[AlKey]

use formula

=TRIM(A2)
and copy it down. Then copy the entire range and Paste Special, Values in A2. This will replace data with trailing zeros with data without them.

[ft9a]

Awesome, thanks, I did not see the formula in there, did not think you copied over.

[Tony Valko]

One option is to use this macro:

The macro at this website will remove all leading/trailing
and multiple interspersed char 32 space characters.
It will also remove and/or convert char 160 non breaking
spaces into standard char 32 space characters. It will
work on text or numbers and the numbers will be
converted to true numeric numbers.

I use this macro dozens of times every single day!
It's a real time saver.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

[AlKey]

You're welcome and thank you for your feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).