Facing a very strange situation while using Excels LEFT, RIGHT, LEN functions on a cell. Cell contains a numeric value (00361941) to be exact. When I try to use LEFT(Cell,6), it gives 00361 (should be 003619), when I use RIGHT(Cell,6), it gives 61941 (should be 361941).
Then I tried to find the length of the cell, it strangely, it gave me 10, when the characters count is 8.
I tried using CLEAN(), TRIM(), CLEAN(TRIM()) functions to remove any spaces or other characters, still getting the same results. I even tried to do a CTRL+H to replace any CHAR(10), CHAR(13) and CHAR(160) characters with some other value, still same results.
Any leads to resolve or find out the exact issue, please.
3
You can research the text using formula:
=CODE(MID($A$1,A2,1))
It shows the character codes char by char.
Invisible Characters
-
After playing around for a while, I concluded it is safer to use
UNICODEandUNICHARas indicated by nkalvi in your comments. I put your number between two characters described as U+200C ZERO WIDTH NON-JOINER on [this page])(https://invisible-characters.com/). -
I used the following formulas to deal with the issue:
=UNICODE(LEFT(A1,1))&", "&UNICODE(RIGHT(A1,1)) =SUBSTITUTE(A1,UNICHAR(8204),"")(
8204is decimal,200Cis hexadecimal.) -
The screenshot tells the rest of the story.
-
Could you share some feedback about what happened in your case to make your post useful for others?