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

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?

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

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?

--
Biff
Microsoft Excel MVP


"Lake Oswego" wrote in message
...
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?

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

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.

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Lake Oswego" wrote in message
...
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?


.

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

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Lake Oswego" wrote in message
...
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.

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Lake Oswego" wrote in message
...
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?


.

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

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

"Niek Otten" wrote:

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Lake Oswego" wrote in message
...
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.

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Lake Oswego" wrote in message
...
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?


.

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

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

--
Biff
Microsoft Excel MVP


"Lake Oswego" wrote in message
...
That was it. Char(160). Why didn't I think of that? Thanks a million!

"Niek Otten" wrote:

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Lake Oswego" wrote in message
...
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.

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Lake Oswego" wrote in message
...
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?


.