# Excel Help (and a litle bit of SQL Server) **URL:** \ **Category:** Factual Questions\ **Created:** [November 7, 2014, 11:57pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028 "2014-11-07T23:57:09Z")\ **Posts on this page:** 12\ **Page:** 2
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [November 9, 2014, 5:26pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/21 "2014-11-09T17:26:03Z")
> [@Reply](#): > > You know, try this function first: > =unicode(a1) to return the unicode codepoint of the first character in that cell. Does it show anything at all for the blank ones? Rats, evidently UNICODE() is only on Excel online and Excel 2013. (I’m running Excel 2010) ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [November 9, 2014, 5:30pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/22 "2014-11-09T17:30:51Z")
> [@Enright3](#): > > 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! > [@](#): > > I interpreted this to mean use a function to cast it as a readable ascii string; which did using vba and the asc() function. It’s weird, when I tried the vba function failed. I placed a tab character into a test cell, ans using the same vba code it correctly return the ascii value; so I know I coded it right. 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. ---
**Author:** ![Martin\_Hyde](https://avatars.discourse-cdn.com/v4/letter/m/47e85d/32.png) [@Martin\_Hyde](https://boards.straightdope.com/u/Martin_Hyde)\ **Post date:** [November 9, 2014, 5:54pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/23 "2014-11-09T17:54:22Z")
> [@Reply](#): > > Maybe I’m misunderstanding this, but wouldn’t casting it as string still result in a non-displaying character? I mean aren’t " ", " ", and " ", all strings even though they’re different characters? 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. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [November 9, 2014, 6:35pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/24 "2014-11-09T18:35:34Z")
> [@Enright3](#): > > Not true. If I put a char(9) (tab character) in a cell it comes back as a zero length field. 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 ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [November 9, 2014, 6:46pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/25 "2014-11-09T18:46:06Z")
> [@Enright3](#): > > I’m seeking the knowledge to determine what is in any cell similar to this and I thought it would be quick and easy answer for an excel expert. 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 ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [November 9, 2014, 6:51pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/26 "2014-11-09T18:51:43Z")
He’s tried that in the OP. It’s probably not ASCII, whatever it is. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [November 9, 2014, 6:57pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/27 "2014-11-09T18:57:53Z")
> [@Reply](#): > > 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. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [November 9, 2014, 10:34pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/28 "2014-11-09T22:34:00Z")
> [@RaftPeople](#): > > 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? ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [November 10, 2014, 1:14am UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/29 "2014-11-10T01:14:11Z")
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. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [November 10, 2014, 3:26pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/30 "2014-11-10T15:26:38Z")
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. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [November 10, 2014, 9:27pm UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/31 "2014-11-10T21:27:03Z")
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? ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [November 11, 2014, 11:46am UTC](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028/32 "2014-11-11T11:46:24Z")
Our OP has disappeared into a zero-length string ☹ EXCEL: A QUANTUM-MECHANICAL MURDER MYSTERY COMING SOON TO A THEATRE NEAR YOU [Previous page](https://boards.straightdope.com/t/excel-help-and-a-litle-bit-of-sql-server/704028.md?page=1)