Vlookup N/A formatting Issue

[matan]

For a while I have a problem using Vlookup
Sometimes when I want to compare two different columns sent to me by two different sources the Vlookup come back with N/A although I know and see the date is matching.

I know its a formatting problem but I can not fix it
Retyping solve it but its crazy
Also when I copy only the exact phrase from the function row its works.

I am using office 2003

I attached an example where I try to compare between column A to B

[squiggler47]

Not sure exactly why, i'm guessing that there is an extra character perhaps char(160) in column A!

Try


=VLOOKUP(B2,A$1:A$2257,1,TRUE)

[martindwilson]

nope there is a strange char, it shows as char 63 but its not so its probably from some other code set
i cant delete it but you could use
=VLOOKUP(MID(B1,2,LEN(B1)-2),A$1:A$2257,1,FALSE)

[squiggler47]

yes it looks like column B starts with an extra invisible character!

place this in D3 and copy across to S4
=CODE(MID($B1,COLUMN()-3,1))

and this in D4 to S4
=CODE(MID($A2,COLUMN()-3,1))

Notice the first cell is different, all the rest match offset by one, this is why you get no result the strings are different!

[martindwilson]

to delete it
put = left(b1,1) in a spare cell
then copy it
open find replace
select find
paste in with ctrl +v
leave replace field blank
replace all

[matan]

Thank you!!!!!!!!!!!!!!

Its works

Thank you!!!!!!!!!!!

to delete it
put = left(b1,1) in a spare cell
then copy it
open find replace
select find
paste in with ctrl +v
leave replace field blank
replace all