Why are two identical-looking values not equal to each other?

Just because data looks identical, it doesn't mean it is.

That's the key message of this video, which demonstrates how two values that look the same are actually different.

'Hello world' is contained in cells B2 and B3, each referred to as Value A and Value B, respectively. After testing them for equality using =𝙱𝟸=𝙱𝟹, FALSE is returned. Their lengths are then checked for any trailing spaces. There turns out to be one in Value A, but even after removing it, the equality test does not change.

The difference only becomes apparent after returning the Unicode numbers for the spaces that separate the two words. This is done using =𝚄𝙽𝙸𝙲𝙾𝙳𝙴(𝙼𝙸𝙳(𝙱𝟸:𝙱𝟹,𝟼,𝟷)).

Unicode is a universal encoding standard that has a unique value assigned to every character, symbol, and script used in languages worldwide. Its purpose is to ensure the consistent representation of text across different platforms and devices.‍

Inside UNICODE, MID is nested and the range is specified before putting a 𝚜𝚝𝚊𝚛𝚝_𝚗𝚞𝚖 of 6 as both spaces are the sixth character. A 𝚗𝚞𝚖_𝚌𝚑𝚊𝚛𝚜 of 1 is also added to extract only that character.

The Unicode numbers returned are 32 and 160, proving there is a difference. Value A's space is copied across and pasted to replace Value B's. With both now featuring spaces that use number 32, all things are equal and TRUE is returned.

You might encounter this problem if you copy and paste something from the web. Nevertheless, it's worth being aware of when you're cleaning and transforming your data because it can mess up your analysis in a very sneaky way.

For more Excel tips and tricks like this, check out our Video Tutorials page.