7

Excel is refusing to open some of the files I programmaticaly create. I think what is happening is that it pukes because a particular column has very long strings (up to 1850 chars), but for the purposes of troubleshooting when I make the strings shorter (900 chars), it opens.

Question #1 : What is the longest string I can fit in a cell? Some google results say 1024 or 7,000ish but I have found 1000 to be too long.

Question #2 : I've tried writing my files in both XML and XLS format, but both have this same string-length-problem. Is there a format that Excel will read that will allow me to fit my 1850 length strings?

4
  • 1,800ish is the size of three modest paragraphs.
    – rlb.usa
    Aug 30, 2010 at 21:55
  • 1
    You need to specify your version of excel, as this is one of the many things that is different between the versions.
    – Jarvin
    Aug 30, 2010 at 21:56
  • It's MS Excel 2007
    – rlb.usa
    Aug 30, 2010 at 21:58
  • What is the code you're using to create it? Maybe the issue is in the filetype not being specified.
    – daneshjai
    Oct 13, 2016 at 0:36

5 Answers 5

6

According to this there was a 1k characters per cell limit in excel 2003 which increased to a 32k characters per cell limit in excel 2007. If your program is programmaticaly creating an excel 2003 (.xls) and not a excel 2007 (.xlsx) file, then this could be causing the issue since the .xls format doesn't support more than 1k characters per cell.

You shouldn't be having issues with a XML or XLS format going directly into Excel 2007, since excel 2003's limit shouldn't be involved, but I can't test it out since I don't own Excel 2007, but hopefully this has been helpful regardless.

1
  • I think that your answer is correct, but it doesn't help me, and it still rejects my 1,800 << 32,000 characters. I'm going to wait and see if anyone can come up with any thing.
    – rlb.usa
    Aug 30, 2010 at 23:10
7

Even Excel 2003 can easily handle 32,767 characters in a cell, but versions before Excel 2007 only display the first 1024 characters in-cell (MS source).

You may have run into a known issue that truncates strings at 911 characters. I've experienced this before when transferring data over an ADO connection.

The following link pertains to Excel 2003 but it certainly sounds like the same problem you are having.

http://support.microsoft.com/kb/818808/en-us

3

Official Excel limits say it should be fine:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

1
  • yup this is the right link. Mar 13, 2013 at 21:14
1

In case you get your data from ADO, try the codes below. It worked for me.

Activecell.value=LEFT(YourRecordset("FieldName"), LEN(YourRecordset("FieldName")))

Basically, you get the whole content of the field, but in a different way. Since the length of the contents over 900 (to my experience), you have to use an alternative way. You do not have to stick with the LEFT function; the RIGHT function also works.

1

In Excel 2013 (and I assume in Excel 2007 and later) the length of a string displayed in a single cell is 1024 characters. I discovered this by accident when I wrote a longer string created in VBA into a single Excel cell, which was the last cell in the column to contain data. The usual display of a long string in cells to the right terminated after 1024 characters. However, the len function correctly returned the actual size of the string and copying the string (which was a list of email addresses) and pasting it into Outlook gave the correct string. It seems that the longer string available in Excel 2007 and later work fine, but will not display completely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.