Forum Discussion
Data looks to be the same, but EXACT function says it's not - how to I find out why?
I've got two text strings that to the eye appear to be the same. Even an IF function comparing the two says they are the same. However, when I use the EXACT function it says they are not.
The reason I got into this is I was actually comparing data in two sheets and getting values using a combo of INDEX & MATCH functions. I hit some rows that didn't return data. I looked a the instances where it didn't work and I got a #N/A and to the eye they appear to be the same. I then copied one of the instances to another sheet and just tried to figure out why.
My first check was with a simple IF statement and it takes the true path. =IF(B1=B2,"ok","NO")
I then did a simple EXACT and it gave me FALSE =EXACT(B1,B2)
I even tried a CLEAN of each one first and still got FALSE =EXACT(CLEAN(B1),CLEAN(B2))
This is driving me crazy. Any help will be greatly appreciated.
Hi John,
That's non-printable character between third and fourth zeros in B1. You may remove it manually or SUBSTITUTE if that's repeating in many cells.
Please see attached.
Hi John,
That's non-printable character between third and fourth zeros in B1. You may remove it manually or SUBSTITUTE if that's repeating in many cells.
Please see attached.
Thanks both of you for you answers. I had thought the CLEAN function would take care of this until I read the details of code values it removes - and in this case the UNICODE value of 65279 is no where near what limited values CLEAN takes care of.
At least now I know I'm not crazy. I'm going to look more closely at the data load process I have to see if it's introducing these errors. What's bad is I bring in 50K rows and 20 have this issue. Oh well.
Again thanks for the answers.
Hi John,
Yes, CLEAN is only for first 30 or so characters from the ANSII table.
If you have only 65279 inside perhaps the easiest is to remove them manually - copy the character into clipboard (stay next after 3rd zero, Shift, right arrow, Ctrl+C) and replace on you entire range on nothing (Ctrl+H).
If you have bunch of different unicode characters inside when macro to remove them will be better. Starting point is here https://excelribbon.tips.net/T006126_Getting_Rid_of_Non-Printing_Characters_Intelligently
John,
using LEN() gives you a clue that both values have different length.
