data looks same but showing different when using formula

[sumonrezadu]

Can anyone help telling me what is the difference between two data? I checked carefully but failed to find out. Thank you in advance for solution.

[benishiryo]

hi sumonrezadu. the difference is the space. A1 contains a normal space while A2 has a non-breaking space. to change or compare, you can use:
=SUBSTITUTE(A1,CHAR(160)," ")
or
=SUBSTITUTE(A1,CHAR(160),CHAR(32))

you can have a good read here:
http://en.wikipedia.org/wiki/Non-breaking_space

[Pete_UK]

Although they both appear to have a space between the two names, the first one has a normal space character (code 32), whereas the second one has the non-breaking space character (code 160), so they are not the same. You can use this formula in B1 to change code 160 to code 32:

=SUBSTITUTE(A1,CHAR(160),CHAR(32))

and copy this into cell B2, then when you use:

=B2=B1

this will return TRUE.

Hope this helps.

Pete

[sumonrezadu]

Many many thanks for your help. But the question is how will I solve the problem easily when I get lots of similar data in a column. Each space may be different from other space. This is just one example. How will I solve other pairs in which spaces may be different from the data I attached here? What is the difference between normal space and non-breaking space?

[sumonrezadu]

Thank you very much. Now I fully understand. I thought there could be various types of spaces. Since there are only 2 types of spaces, my problem is completely solved. Thanks again.