Can't find difference between two cells

[Gfletch]

I am having trouble finding out why there is a difference between two cells that appear to contain the same thing. Each cell contains "Mickey Mantle". I checked by using LEN but it says both cells contain 13 characters. I tried using EXACT, and EXACT returns a FALSE result. Then I used the following formula:

[TABLE="width: 319"]
<tbody>[TR]
[TD="class: xl65, width: 319"]=MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1) and
[TABLE="width: 319"]
<tbody>[TR]
[TD="class: xl65, width: 319"]=MID(D5,ROW(INDIRECT("1:"&LEN(D5))),1)[/TD]
[/TR]
</tbody>[/TABLE]

where B5 and D5 each contain "Mickey Mantle"...and this says that the M is different...but I do not know why.

The problem is that I have a data base and need to be able to sort every players career by his Name and then the year of each of his seasons...but these 'differences' cause problems.

Thanks for any help.[/TD]
[/TR]
</tbody>[/TABLE]

 

[konew1]

I don't understand how your formulas will ever do anything except return the first letter of the name.
I suggest you put =MID($B$5,ROW()-4,1)=MID($D$5,ROW()-4,1) into row 5 of an empty column and copy down

 

[Gfletch]

I don't understand how your formulas will ever do anything except return the first letter of the name.
I suggest you put =MID($B$5,ROW()-4,1)=MID($D$5,ROW()-4,1) into row 5 of an empty column and copy down

Thanks for the suggestion. Your formula returns TRUE, but =EXACT(B5,D5) returns FALSE. So there is a difference, but what? Is there a way to examine two cells and not only find the difference, but specifically identify what that difference is?

Thanks again.

 

[Blunder1]

have you tried =exact(trim(b5),trim(d5))? There could be a space at the end that LEN doesn't count..

 

[konew1]

Thanks for the suggestion. Your formula returns TRUE, but =EXACT(B5,D5) returns FALSE. So there is a difference, but what? Is there a way to examine two cells and not only find the difference, but specifically identify what that difference is?

Thanks again.

Did you copy my formula down? which row returned the FALSE, because that is the position of the difference

have you tried =exact(trim(b5),trim(d5))? There could be a space at the end that LEN doesn't count..

Trying TRIM is a good idea, but note that LEN counts spaces.
It may be necessary to use CLEAN() to remove nonprintable characters. in my experience they have only occurred in text downloaded from a database system

 

[Gfletch]

Did you copy my formula down? which row returned the FALSE, because that is the position of the difference



Trying TRIM is a good idea, but note that LEN counts spaces.
It may be necessary to use CLEAN() to remove nonprintable characters. in my experience they have only occurred in text downloaded from a database system

This didn't work. I isolated every character in each example and then used =IF(B5=D5, "TRUE","FALSE") to test them...the only character that appears to be different is the space. I went back to the full data base and tried eliminating all spaces, but it still doesn't sort properly. Is there more than one type of character that appears as a space?

 

[bkaehny]

If you have access to the database, can't you just update all of one of the Mickey Mantle values to the other so that everything groups and sorts properly?

 

[konew1]

This didn't work. I isolated every character in each example and then used =IF(B5=D5, "TRUE","FALSE") to test them...the only character that appears to be different is the space. I went back to the full data base and tried eliminating all spaces, but it still doesn't sort properly. Is there more than one type of character that appears as a space?

How do you use =IF(B5=D5, "TRUE","FALSE") to test each character? if you are progressively removing some characters from B5 and D5 to test the remaining characters, then your result is not going to be reliable.

Question: Did you put =MID($B$5,ROW()-4,1)=MID($D$5,ROW()-4,1) into row 5 of an empty column and copy down?
What result did you get in row 5? What result did you get in row 6? what in each of the rows up to row 19?

 

[Gfletch]

How do you use =IF(B5=D5, "TRUE","FALSE") to test each character? if you are progressively removing some characters from B5 and D5 to test the remaining characters, then your result is not going to be reliable.

Question: Did you put =MID($B$5,ROW()-4,1)=MID($D$5,ROW()-4,1) into row 5 of an empty column and copy down?
What result did you get in row 5? What result did you get in row 6? what in each of the rows up to row 19?

I eliminated all but one character, the same character, from both cells. comparing each to the other I got TRUE on everything except for the space.

I did put the =MID etc - got a return of TRUE

 

[konew1]

try
=exact(clean(b5),D5)
then =exact(b5,clean(d5))
what do you get?