• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

True False formula lying to me?

S

Samantha S

New Member
Ok I'm pretty certain Excel and I are not meant to be friends this week.

I am in the process of validating information between Microsoft Excel 2007 (File extension is .xlsx) and Microsoft Access data (our lovely ODBC connection).

I am doing a simple =S2=S3 and getting a result of false. When I can very clearly tell the value in both boxes match (e.g. PO BOX 123456).

The cells are both formatted to genearl, there are no leading or trailing spaces, and there are no extra spaces between the words/numbers. Any ideas as to why Excel feels the need to lie to me? Is there a simple formula fix I can employ other than the = =?

Mind you I performed the same exact task on Tuesday, without fail. I have not uploaded a document due to the private nature of the data I am dealing with.
 
If I copy PO BOX 123456 from your post into S2 and S3 in Excel and then compare them using =S2=S3, Excel returns TRUE. There must be a difference between the two values in your worksheet.

The difference could be data type, eg. a numeric 123456 versus a string 123456 which you can check with the TYPE() function.

If both are strings then spaces, similar looking characters such as O and 0 would be the most likely culprits. A good check is to use the LEN() function to check the lengths of the strings in each cell.

It sounds like you've already had a good look at this and because the answer is data-centric, I think the best thing would be for you to upload a simple, sample file for us to see. It's hard to guess the reason without the data.
 
Hi, Samantha S!
Joining what Colin Legg wrote, my two cents won't surely aid a lot, but maybe they confirm your assumption: Excel is an application, female; formula too;... so why do you expect that they don't lie to you? I don't mean always but... :p
Regards!
 
Thank you both for the assitance! Here are the two cells that are proving me difficulty (pulled just the necessary data). I've done the True/False check in cell A5 = false. I also did the Type and LEN formulas for each cell in columns C & D..they are a match. From what I can tell items are formatted the same and visually appear to be the same...?
 

Attachments

  • Data validation.xlsx
    8.8 KB · Views: 10
I've tried a number of copy/paste fixes. We get a request from the customer that we use to input the data into the sytsem. I verified with the individual who keyed the data how they entered it. Originally, she manually typed it into the system. I then had her copy/paste from the request to the system and resave the data. I exported the top two lines (gray header and first instance of the PO Box) from the database via Microsoft Access and then copy/pasted the second instance from the request, to do the true/false comparison.
 
Hi, Samantha S!
Give a look at the uploaded file. I added the ASCII code for each char of both strings and the issue is the famous damned CHR(160) which is a false space. In fact it isn't a false space, it's a true space but originated in non-Windows data sources. Check column L.
Regards!
 

Attachments

  • True False formula lying to me_ - Data validation (for Samantha S at chandoo.org).xlsx
    10.2 KB · Views: 6
Fabulous! Thank you SirJb7! I can now move on with my life, haha! This is most helpful, I can validate the data and note properly. I appreciate all of the help and feedback from everyone! Oh Excel, how I love to hate you some days...
Merry Christmas & Happy Holidays all!
 
Hi, Samantha S!
The file updated, I added a 3rd line with this formula in A4 and the comparison in A7 it's Ok:
=SUSTITUIR(A3;CARACTER(160);CARACTER(32)) -----> in english: =SUBSTITUTE(A3,CHAR(160),CHAR(32))
Regards!
 

Attachments

  • True False formula lying to me_ - Data validation (for Samantha S at chandoo.org).xlsx
    10.5 KB · Views: 3
Yep, it's a sneaky one, but they are different. The difference is the 'space' character between the X and the 4. In A3 it is a non-breaking space character.

A simple way of checking this: I used the MID() function to split out all of the characters in each cell and then I used the CODE() function to get the character code for each. The ASCII code for the space in A2 is 32; the ASCII code for the space in A3 is 160.
 

Attachments

  • Data validation(1).xlsx
    10.1 KB · Views: 6
Hi, Samantha S!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
I really do appreciate this message board. You've taught me a lot in the last several days with all my weird quirky EXCEL "issues," learning is always a bonus. You all are super friendly too! Thank you COlin for the additional feedback, I will share this with my team, to save them the headache as well.
 
@GFC
Hi!

No, it won't help. Have you tested it in the OP uploaded file?

From your posted link:
"Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document."
And Chr(160) is not a formatting form of space, Chr(32).

Regards!
 
You must log in or register to reply here.
Back
Top