Special Characters in a join

I had a friend of mine that was having a problem performing a join.  Some of the information he downloads from the web and pastes into an excel spreadsheet that he then imports into IDEA.  He was doing the join based on the persons name. 

When I looked at the two files and compared the name fields they looked exactly the same.  The field was in the format of First Name Last Name.  I then though that maybe one of the fields had spaces at the end that might have been screwing up the join so I used the view spaces function in IDEA to check it out.  Turns out that there were no spaces at the end but I noticed in one of the files the space between the first and last name was not showing up as a space, interesting!!  So I created a virtual field and used the following function to see what character that looked like a space actually was: @Ascii(@Mid(FIELD,4,1)), I used 4 as I knew that several of the records had that non-space in position 4.  Well it turns out it returned 160 as the ASCII code, go figure, definitely not a space.  So I then to the internet to find out what it is and it turns out it is a non-breaking space, so it looks exactly like a space but for IDEA it is a totally different character.  To fix the problem I created another virtual field and used the following formula: @Replace(FIELD, @chr(160), " "), this replaces the non-breaking space with a regular space.  So he was now able to do the join with the virtual field.

I guess the moral of the story is that when you see a join not working as expected you might have to dig a bit deeper to find out what the real problem might be.