13

I have a load of data in a csv file that I'm importing into Excel, most of it is fine, but one of my columns contains up to 1000 characters of data. Unfortunately, Excel has taken it upon itself to assume that's more data than I need and seems to be cutting it short (at 255 chars).

How can I increase this limit and retain all my data?

  • What do you mean by "Unfortunately, Excel has taken it upon itself to assume that's more data than I need and seems to be cutting it short." is it not displayed properly? what happens if you put =LEN(A1) into Excel where A! is the reference to your text? – Peter Albert Jan 28 '13 at 13:15
  • It says 255, and when i look at the data In the formula entry box it just suddenly stops. I can display the full data in open office, but as soon as I paste to excel it seems to be limiting to 255 chars. The reason I am not using open office is because I can past as data or do a vlookup in that. – SwiftD Jan 28 '13 at 13:36
  • Excel can handle texts longer than 255 chars. However, if the cell is filled by VBA, it is a bit complicated. What's your source? How do you get it into Excel? – Peter Albert Jan 28 '13 at 13:47
  • I have a csv export from magento. If I open in OO it has the full text. If I open in Excel its cut short. Also gets cut short when I paste from OO to excel and I cannot manually type any more in the cell, it just gets cut off at the same point as soon as I leave the cell. – SwiftD Jan 28 '13 at 14:12
  • Maybe something is off with the CSV? Can you update your question and post some lines? Also, maybe the delimiter and separator settings on your machine start acting up? – Jan Doggen Jan 28 '13 at 16:07
14

There is a weird bug in Excel. I'll explain here how to avoid (not fix, just avoid) it, and maybe it will fixed soon by MS Office programmers. Maybe it even WAS fixed in Excel 2013, I did not open it yet.

So, this is the problem.

The maximum length of the text cell is 32767, and it's OK.

The maximum length of the number cell is 255, and it's OK.

But if you have a cell that calls to number parser, fails and then calls to text parser, here will be the big mess.

For example:

...,"This is a cell with 30,000 characters........",...

will work.

But if you'll put a little minus at the start, as in

...,"-This is a cell with 30,000 characters........",...

you'll see only 255 first text characters, because EXCEL thinks it's a negative number.

Hope it helps.

| improve this answer | |
  • 3
    This is the weirdest and most stupid bug I've ever seen in commercial software. It's literally driving me insane how on Earth can you pay for this kind of sh*t. – astrojuanlu Oct 22 '14 at 11:49
  • 1
    Very good explanation, thank you. Unfortunately, I can confirm, that - 3 years later - the bug is still present in Excel 2016... – BurninLeo Jan 24 '18 at 19:12
  • Well, @BurninLeo, I can say you that three days after my original answer, I has made a chat call to MS Office developers Center, and gave them a link to this Super User Topic. – khitron23 Jul 29 '18 at 17:41
  • The problem seems to be resolved with Excel 365 ProPlus version 1902. – Carsten Franke Aug 14 '19 at 8:59
  • Can confirm this feature/bug is still in Excel 2016 as of April 2020. – geeves Apr 8 at 15:52
1

I ran into this problem with a csv file that had some long text fields that sometimes began with a dash. It seems that Excel's import tries to process the field as a number because of the dash, and the number import trims it to 255 characters. When that fails, it imports it as text, but only the first 255 characters.

To avoid the problem, I had to import the csv instead of just opening it. In the import wizard, I selected the column that was having trouble and switched its type from General to Text. Then Excel won't even try to parse it as a number, and I get the full column width.

| improve this answer | |
0

Firstly, Thank you all for your help in trying to solve this. It turns out excel can quite happily accommodate more than 255 chars to a cell (presumable up to 32,767 as previously mentioned if you've got the ram for it.

So why didn't is work?

The short answer is I'm not sure. The first time I brought the data into excel I exported as a csv then opened in excel. This cut the columns short (at 255). (the csv file contained all the data when viewed through notepad but not when saved again through excel).

The column in question did contain some semicolons, but this didnt correspond to the cut off point (always 255) and I wasnt using ; as a delimiter.

My solution wast to do an export as an xls file. This has since been saved successfully as a csv file with the full data.

Hope this helps someone even though I'm still not sure exactly what the issue was here.

| improve this answer | |
  • Simple Googling results in a lot of forum post of people who have the same problem; it's probably just a bug in the Excel CSV import routine. – Marcus Chan Jan 28 '13 at 16:41
0

According to http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx, Excel 2010 only supports a 255 character column width.

Edit: Is this the problem you're talking about, Webweaver? Does Format > Cells > Wrap Text make the rest of the characters visible?

Edit: Never mind, this answer is somewhat unrelated to the problem at hand (possibly related somehow, but it doesn't answer the question)

| improve this answer | |
  • No, that's the COLUMN WIDTH. From the same table, the "Total number of characters that a cell can contain" is 32,767 characters. – Vicky Jan 28 '13 at 15:35
  • @Vicky, isn't that the problem the asker is experiencing? Or did I misinterpret the question? – Marcus Chan Jan 28 '13 at 15:51
  • @nixda real? yes real annoying. I cant believe excel cannot handle more the 255 chars. There is a way to do this! – SwiftD Jan 28 '13 at 16:01
  • @Marcus I dont understand what it means by column width. This isn't just a display issue the data isn't there. It gets cut short. when I save as a csv the data isn't there. when i do len(cell) the result is 255. – SwiftD Jan 28 '13 at 16:04
  • @MarcusChan, the number of characters should be limited to 32767. The user is experiencing it being limited to 255. What you were referring to was the column width which is to do with how the column is displayed, which is not the OP's problem. – Vicky Jan 28 '13 at 16:05
0

Generally, this is an old limitation that no longer applies to modern versions of Excel (2013 anyway). However, if a workbook was originally created in an older version, cell objects that were modified (i.e. thereby instantiated with particular properties and attributes) in the older version will, apparently, retain the 255 character limitation even when opened in newer versions of Excel!

A fix:

  1. Using a modern version of Excel, in a different, unused cell on the worksheet, recreate whatever formatting and/or merged-range condition is required for the problem cell.
  2. Be sure that it is set to General or Text format.
  3. Cut or copy and paste the new, recreated version of cell (or merged range) and paste it over the old, bad version.
  4. Now, when entering data into the cell, the problem should no longer manifest.

As WebweaverD subsequently posted, he found a solution to his csv import problem. Also, if the cells are being populate via VBA, then the situation gets more complex, as mention by other replies. But if the data is being manually entered, or copied/pasted from another application into a legacy workbook, the problem may be caused by this legacy cell-attribute issue.

| improve this answer | |
0

I had a similar problem, all my values was in format(to can convert numbers to numbers, text to text, but it was cut long texts:

...,="Long Text........",...

if you'll put a = at the start, you'll see only 255 first text characters, because EXCEL thinks it's a number, so remove = at start.

Hope it helps.

| improve this answer | |
  • 1
    Is this different than what someone else may have already mentioned in an answer? This may need to be flagged and you can ask a moderator to convert to a comment since you don't have enough rep to do on your own. – Pimp Juice IT May 7 '17 at 15:32
0

Workarounds (available in Excel 2016)...

Option A. Get Excel to run through the text import wizard (by using something like a .txt file extension or use .xls extension, accept the warning, select the first column, and do Data => 'Text to Columns'). Then change the column type from General to Text near the end of the wizard.

Option B. Use Get & Transform. Data => New Query => From File => From CSV.

| improve this answer | |
  • Consider adding some reference to this answer supporting what you state. – Pimp Juice IT Sep 8 '17 at 18:51

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.