Rats, evidently UNICODE() is only on Excel online and Excel 2013. (I’m running Excel 2010)
Can’t you just upload it to Excel online? It’s free and private if you’d like it to be.
The anticipation is killing me! Upload it and solve the mystery!
If it’s causing this many problems, it’s likely not an ASCII character. ASCII is a very limited subset of what English speakers used back in the 90s and prior, and most of today’s communications cannot be sufficiently encoded in its limited character set.
For all we know the creator of the spreadsheet might’ve created it in another operating system, or another language operating system, that ended up inadvertently encoding some sort of space, unfamiliar punctuation, or even a whitespace/control character. You really have to look at the character mapping for it in binary/hex to find out what is being stored in that cell.
Right, but he’s wanting to know what characters are in the string. They’re non-visible characters. I’m not super familiar with VBA’s debugger, but some debuggers if you inspect the string it will show you certain special characters like
(new line) or \r (carriage return) or (tab) (those are C characters, I think it’s things like VbCr, VbLf, and VbTab in VBA) in the run-time variable. Sometimes not, in JavaScipt you’d need to loop through all the characters in the string and print their ASCII codes to figure out what was there, for example.
Not correct.
I just tested it with the following procedure:
Zero length string
Cell C3=“”
Cell C4=LEN(C3)=0
String with a TAB
Cell C3=CHAR(9)
Cell C4=LEN(C3)=1
Note: If excel returned a 0 in that case it would deviate from most other languages, which I would think they would not want to do
Just convert the chars to the ascii code using CODE
Example (related to my previous post):
Cell C5=CODE(MID(C3,1,1))=9 (Tab char)
MID extract’s a string from the middle of another string
CODE returns an ascii value
He’s tried that in the OP. It’s probably not ASCII, whatever it is.
He also incorrectly stated that LEN(CHAR(9)) returns a zero when in reality it returns a one - which means it’s not clear which info in the OP can be trusted.
The plot thickens.
OP, care to share?
I’m still trying to get him to tell us if it’s an xls or an xlsx. If he tried to do a hex diff on an xlsx, then no wonder he got no useful results. That’d be a compressed file in zip format.
I wonder if perhaps the original file from his client hasn’t been through a conversion or two. Like maybe it originated in a different version of Excel or was converted from Open Office or ???. Or even was created and saved in Open Office but in OO’s implementation of the xlsx format.
Those sorts of mixed-vendor processes are famous for producing results that are mostly correct, but have weird glitches that may not manifest in the ordinary UI, but will appear under the strain of more complex processing. Such as import to SQL.
Based on the symptoms I think it’s just a zero length string.
A zero length string will cast as INT in SQL Server ok, but it will produce an error if trying to cast as NUMERIC, so my guess is that the destination field is NUMERIC and the cell is zero length string.
As long as we’re seeking clarification from the OP, are you sure the TYPE() function returns 0? That’s not one of the return values (at least in Excel 2013). An empty string should return 2.
I think LSLGuy might be on the right track regarding the file format. It could be a completely different format (like DBF, HTML, or something more obscure) with an XLS/XLSX extension slapped on it. If that’s the case, Excel might be able to open it but with some weird values in some cells.
OP, if you open it in a text editor, do you see anything recognizable?
Our OP has disappeared into a zero-length string 
EXCEL: A QUANTUM-MECHANICAL MURDER MYSTERY
COMING SOON TO A THEATRE NEAR YOU