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.
@globalexcelsummit 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 =𝚄𝙽𝙸𝙲𝙾𝙳𝙴(𝙼𝙸𝙳(𝙱𝟸:𝙱𝟹,𝟼,𝟷)). 𝘜𝘯𝘪𝘤𝘰𝘥𝘦 𝘪𝘴 𝘢 𝘶𝘯𝘪𝘷𝘦𝘳𝘴𝘢𝘭 𝘦𝘯𝘤𝘰𝘥𝘪𝘯𝘨 𝘴𝘵𝘢𝘯𝘥𝘢𝘳𝘥 𝘵𝘩𝘢𝘵 𝘩𝘢𝘴 𝘢 𝘶𝘯𝘪𝘲𝘶𝘦 𝘷𝘢𝘭𝘶𝘦 𝘢𝘴𝘴𝘪𝘨𝘯𝘦𝘥 𝘵𝘰 𝘦𝘷𝘦𝘳𝘺 𝘤𝘩𝘢𝘳𝘢𝘤𝘵𝘦𝘳, 𝘴𝘺𝘮𝘣𝘰𝘭, 𝘢𝘯𝘥 𝘴𝘤𝘳𝘪𝘱𝘵 𝘶𝘴𝘦𝘥 𝘪𝘯 𝘭𝘢𝘯𝘨𝘶𝘢𝘨𝘦𝘴 𝘸𝘰𝘳𝘭𝘥𝘸𝘪𝘥𝘦. 𝘐𝘵𝘴 𝘱𝘶𝘳𝘱𝘰𝘴𝘦 𝘪𝘴 𝘵𝘰 𝘦𝘯𝘴𝘶𝘳𝘦 𝘵𝘩𝘦 𝘤𝘰𝘯𝘴𝘪𝘴𝘵𝘦𝘯𝘵 𝘳𝘦𝘱𝘳𝘦𝘴𝘦𝘯𝘵𝘢𝘵𝘪𝘰𝘯 𝘰𝘧 𝘵𝘦𝘹𝘵 𝘢𝘤𝘳𝘰𝘴𝘴 𝘥𝘪𝘧𝘧𝘦𝘳𝘦𝘯𝘵 𝘱𝘭𝘢𝘵𝘧𝘰𝘳𝘮𝘴 𝘢𝘯𝘥 𝘥𝘦𝘷𝘪𝘤𝘦𝘴. 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. #exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit ♬ original sound - globalexcelsummit
'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.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.