Two cells look identical but give false response to "Exact".

[Lake Oswego]

I am trying to compare two columns of data. Col A is emailed data; Col C is
cut and pasted from website. All 400 of Col C is in Col A, but only 80 show
as Match. They look and LEN the same, but Exact is false in all but the 80.
I can't modify Col C to complete Match unless I can figure out what's wrong
with cells in Col C. Any ideas?

 

[T. Valko]

They look and LEN the same

The EXACT function means just that *exact*. The case must match exactly.

=EXACT("ABC","AbC")

= FALSE

Whereas:

="ABC"="AbC"

= TRUE

Are you sure you want to use EXACT?

 

[Lake Oswego]

problem is that they are not exact even though they look exact. My Match
formula is not working even though it should. I have the name "T S Eliot" on
two lists and they are the same name but Match won't show the match. If I
retype the name T S Eliot, then the match works. Why should I have to retype
400 names? What is wrong with my cut & paste list? Exact says there are
different even though I can't figure out why they are different.

 

[Niek Otten]

What is a space in one is probably a non-breaking space in the other (the
website one).

Find and replace CHAR(160) with a space.
To do that, enter the formula =CHAR(160) in a cell and copy it to paste in
the find and replace dialog.

 

[Lake Oswego]

That was it. Char(160). Why didn't I think of that? Thanks a million!

 

[T. Valko]

Another option...

If you do this on a regular basis (I do this myself) there is a macro at
this site that will "clean" your data of the most common whitespace
characters that cause these problems.

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