Odd Behaviour - Can't match.. 2 cells equal, not exact? why?

[AdamParker]

Hello,
I'm trying to compare two spreadsheets, the data I'm using is "equal" to each other, but is not exact... and I don't know how I could compare the two...

When I try to vlookup and use exact (when it's not exact - data not reliable) I get no results returned.

When I try index/match, I get no results either.

I've attached a spreadsheet which contains A1 and A2, each of these cells is from a different data source.

If I overwrite A1 with the actual text I get a match... but I can't possibly do this to all of my data...

Any suggestions on how I match these?

I tried vlookup(trim(A1).. or left(A1... but any of these still dont give me an exact match..

Any help would be appreciated.
Thanks

[NBVC]

If you do a =CODE(LEFT(A1)) and copy down, you get 2 different codes for the left-most character.. so they are not exact... also noticed that the =CODE(RIGHT(A1)) is inconsistent for the 2....

It revealed that the character code for A1 is 63... and the only way I was able to match them us to get TRUE was with formula:

=EXACT(MID(A1,2,LEN(A1)-2),A2)

[davegugg]

Strangely, it looks like the data in A1 has both a leading blank spot and a trailing blank spot. This formula will show if they are exact, minus the blank spots:

Please Login or Register  to view this content.