# Excel: Text in Text-formatted cells changes to ### after the 255th character



## longtalker (Oct 28, 2008)

Many times in the past when I would type plain text into cells of an Excel spreadhseet, I noticed that at point, the contents of a cell would turn into sharp signs (i.e. ####################). I did not know what triggered this, but I did notice that if I change the cell type from Text to General, the cell's text would come back to normal.

Today I had this happened again, and I was determined to find out what caused it. As it turns out, if you have a Text-formatted type cell and you type more than 255 characters in it, all the cell's contents will turn into sharp signs. In order to revert to the text you must either change the cell's format to General or truncate the text manually to a maximum of 255 characters.

The problem with formatting cells as General in a text-only spreadsheet is that you cannot use characters that make Excel think you are typing a formula; that is, you cannot use hyphens or "keywords" such as "not", etc, without prefixing them with a '. This is why I normally format all cells as Text when I know I will only type text in a certain spreadsheet.

Has anyone else noticed this, and is there a solution to have more than 255 characters in a cell without having to change its type to General? Thanks for any help.


----------



## macropod (Apr 11, 2008)

Hi longtalker,

It seems to me the 'problem' is entirely of your own making.

AFAIK, the only characters that need to be prefixed with a tick mark are a few math operators (=+-) - and then only if they're the first entries in the cells concerned - and cells containing only a single fraction.

Clearly, the choice is your's - use General formatting and deal with the rare exception, or use Text formatting and keep under 265 charcters.


----------



## longtalker (Oct 28, 2008)

macropod,
The problem is not "of my own making" at all. If you knew a bit more about Excel than you do, then you'd also know that what I am describing is a known bug in Excel - one that makes contents of Text cells that are between 256 and 1024 characters in length be displayed as sharp signs. You can widen your knowledge here, for example.


----------



## macropod (Apr 11, 2008)

Hi longtalker,

Says who it's a bug? The simple fact is that this behaviour is well documented has has been a 'feature' of Excel for at least a decade. A few people complaining about the way the program works doesn't make the behaviour a bug.

As far as I'm concerned, formatting cells as text just so you don't need to use the (very) occasional tick character constitutes a problem of one's own making.


----------



## longtalker (Oct 28, 2008)

OK, I see what you mean. To me it was just an unexpected behaviour (seems rather random that if you have Text cells, then the contents of the cell goes berserk if your character count happens to fall between the magic numbers 256 and 1024). Anyway, I don't want to get into a whole debate with you whether this is a bug or not  Thanks for helping.


----------



## Ricco1962 (Nov 10, 2009)

Hey, macropod I say it's a bug! Because it takes my perfectly readable text and turns it into illegible "sharps".

Both longtalker and myself have chosen to use "text" as a cell type and I as well would also like to know if there is a way to deal with this character limit while still using the text format.

I use excel to help me edit many tab and comma delimited text documents that contain a variety of data (i.e. long numeric strings that are not numbers but will get turned into scientific notation) and as well they are "remarked" with symbols like sharps, slashes and dashes amongst others, which excel "helps" me with by assuming they are parts of formulas (argh!) so "text" is very useful for me as well.

Unless you can state a reason that this 'feature' (note that you put this in quotes) exists wich gives the user some added functionality, it can really only be called a bug... I will grant that it could be called a program limitation, as in '10 years ago' MS may have possibly decided to only allow 256 characters in a cell, which would make sense... 10 years ago, but today (note I use Office 2003) it could have been one of their many upgrades to allow more. Strangely, if you follow the link longtalker provided and the information there is correct it seems that MS may have tired to increase the number of characters allowed in a text cell (purely speculation) but those between 256 and 1024 still don't show correctly so that brings us back to... bug 

But I still say this definitely cannot be called a 'feature' since it serves no useful purpose for the user (that I am aware) and if this was a program limitation then the user should be told, in a dialog, that he/she has reached this limit when it happens not with a 'strange' symbol substitution. 

None of us seem to have an answer to this question so I suppose longtalker and I are out of luck, but please don't criticize or blame us for doing something wrong (cause that's what it felt like) because you don't see to like us choosing text as a format, please just stick to answering the question.


----------



## macropod (Apr 11, 2008)

Hi Ricco1962,

Welcome to the forum. I trust your future posts will have something useful to contribute.


----------



## Glaswegian (Sep 16, 2005)

Ricco1962 said:


> Hey, macropod I say it's a bug! Because it takes my perfectly readable text and turns it into illegible "sharps".


No - it's not. This has been well documented in all versions of Excel I have used over the years (from 97 onwards).

Try typing "Excel specifications and limits" into the search box at the top of the screen and you'll find all you need to know.


----------



## Ricco1962 (Nov 10, 2009)

Sorry Glaswegian I can't buy your argument.  Just because this bug/limit/feature has been documented for years, does not mean it is not a bug.

For it to be a feature it needs to be helpful. While people differ on what _is_ helpful we can see that, for instance, when excel decides that 02-05-09 is a date and reformats it to 02/05/09 (rather than leaving it as a 'part number') we can recognize that it "trying" to achieve something. When it takes my text and displays it as sharps, well thats just kind of buggy.

To be a limit it has to be just "reached". I mean, if the characters got re-displayed sharps and stayed that way then I guess it could be called a limit but when you get to the 1025 character and it shows back up?

If you go to this Microsoft site:
http://office.microsoft.com/en-us/excel/HP051992911033.aspx

they officially state:

Length of cell contents (text): 32,767 characters.
Only 1,024 display in a cell;
all 32,767 display in the formula bar.

I never checked to see if in fact you can get 32,767 characters in a text cell or what happens if you go past this limit, but my excel 2003, again, does not perform as expected. In the case of the 1024 character display limit, I can display more, but between 255 and 1025 it's sharps. And note that they do not state anything about any special situation of displaying 256-1024 characters of text with replacement characters.

Cell with more that 1024 displaying (i.e. 1344):
12 times 7 strings of " 123456789abcdef", cell width=180, hight=120 and alignment-> vertical-> top. Note the space in the string for a total of 16 characters creating 12 x7 x16 (or 1344) characters being displayed! And, yes cell is formated as text.

So I still have to call it a bug. 

As for being helpful? Well, macropd you're correct I should have offered some advice to longtalker.

So, while I am not aware of any way to get the "sharps" to go away within a _text_ cell, I can offer that if you are creating a text cell of that many characters (more than 255) that you format _just that particular cell_ to 'general' and be careful as to what you start the text, with ie avoid formula characters like "-" etc. In my experience the balance of the "mixed text" (I assume it's mixed as you said that you were typing it in by hand) does not get excel's "helpful formating". Also note that I don't do this very often so I can't say that this fix will work in all situation.

Personally I do believe that this discussion of bug vs feature vs limit is, while
does not specifically solve longtalker's question, is helpful because knowing that the problem is with the program and not you will let you move on from the problem and possibly Microsoft will get embarrassed enough to do something about it. 2007 and future excels may not have this issue but those of us still with old versions should still get some support for bugs (sorry I said it again) in the program.

I'm not holding my breath. 

ps maybe my user name should be longwinded...


----------



## B Thomas (Dec 19, 2009)

Thanks Ricco1962 for the useful information.

I ran into this problem when building a form for submitting data from external parties to be parsed into another system.

And I appreciate the provided information that clarifies that this behavior (misbehavior) is not dealt with by MS, and consequently there is no bug fix to expect now or later. 

Knowing this I do regret going for our Excel solution as this “feature” was totally unexpected.

B Thomas


----------



## B Thomas (Dec 19, 2009)

Wait a minute, I looked into what's said here: http://office.microsoft.com/en-us/excel/HP051992911033.aspx.

MS sais that: 
"Length of cell contents (text)
32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar."

This is not what happens. Less than 256 characters will display in both the cell and the formula bar. 
When number of characters is between 256 and 1024 they display in the formula bar but not in the cell. 
With number of characters is larger than 1024 they display in both the cell and the formula bar.

Well, if we should trust what MS sais about "Excel specifications and limits" this is clearly a bug and should be fixed. The question is if MS will fix it???


----------



## macropod (Apr 11, 2008)

Hi B Thomas,

To the extent that this 'feature' was a bug in previous Excel versions, it was that the text would re-display once you got past 1024 characters. The behaviour of displaying the cell's contents as a seies of 255 '#' characters in a cell formatted as text was what was intended.

You will be pleased to know that 'bug' has been fixed in Excel 2007 - once you exceed 255 characters (the maximum permissable column width) in a cell formatted as text, the cell's contents are displayed as a seies of 255 '#' characters. There is no longer a reversion to displaying the text once you get past 1024 characters.

I do not expect MS will fix this purely cosmetic 'bug' in superceded Excel versions.


----------



## B Thomas (Dec 19, 2009)

Tanks for your response macropod!

I'm sorry to say that I'm not happy. 
To begin with I can see no real reason for not displaying the real text in the cells rather than the ####:s. I can see no purpose for that except if it’s suppose to be a limit in what should be displayed. MS says text up to 1024 characters should display (in Excel 2003) but it does not. As it displays text larger that 1024 perfectly, I see no reason, no benefit for this at all. I must say I can't understand why MS intend to have it this way.

I do not have access to later versions of Excel (I'm limited to our companies updates) but if I understand you correct, Excel 2007 only displays 255 characters in cells formatted as text. Is that correct? Whatever purpose there is for this behavior, it totally contradict with our needs in my company. 

It's also rather annoying that this information can't be found easily, because this page http://office.microsoft.com/en-us/excel/HP100738491033.aspx?pid=CH101030621033#WorksheetWorkbook says nothing of the kind. 

If there is a real reason for the ####-behavior, then I think a much better solution would have been to have two different text formats in Excel, one that do the #####:s and another that don't.

B Thomas


----------



## macropod (Apr 11, 2008)

Hi B Thomas,

Text in cells not formatted as Text (eg formatted as General) has always displayed up to 1024 characters, and text in cells formatted as Text has always displayed '#' symbols once you exceed 255 characters. Granted, text in cells formatted as Text has displayed as text once you exceeded 1024 characters in some previous versions of Excel (I don't have a copy of Excel 97 or 95 to test how far back this might have been the case), but I suspect that wasn't intended. And even then, my testing with Excel 2000 suggests the overall 're-display' limit was about 1100 characters - anything after that wasn't displayed, but neither did you get '#' symbols.

Whilst not having any inside information on this, I can think of a number of reasons why MS might have imposed the 255-character limit, quite apart from anything to do with a 2^8 character limit in Excel - which can quite clearly store and display at least 2^10 characters in non-Text cells. For example, it may have had something to do with:
. the maximum column width;
. the transfer of memo field data between Excel and Access via the ODBC driver; or
. text boxes linked to cells only displaying 255 characters.


> if I understand you correct, Excel 2007 only displays 255 characters in cells formatted as text. Is that correct?


Yes. The simple workaround (for displaying up to 1024 characters) is to not format the cells as Text. The only time this should have any impact on the way you work is if your text string starts with a mathematical operator. For that, the very simple workaround is to precede the mathematical operator with a tick mark (which the display will suppress).

Whilst I agree the doco on this could be better, the display change to '#' in cells formatted as Text after 255 characters has been known for at least a decade. FWIW, page 113 of the User's Guide for Excel v5 (from 1993), mentions a 255-character limit in cells and in the MS vba help file for Excel v5 says, in part, "in Microsoft Excel, you cannot have more than 255 characters per cell". Perhaps the change to allowing more than 255 characters, but displaying them as '#' symbols in cells formatted as Text occurred with Excel 95.


----------



## B Thomas (Dec 19, 2009)

Hello macropod!

Still MS says that: 
"Length of cell contents (text)
32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar."
This contradicts the statement that MS intention is to restrict text cells to 255 characters, a statement that I don't claim is incorrect (because I have no other knowledge) but it seams confusing.

Do you have any idea why MS says 1,024 in a text cell?


----------



## macropod (Apr 11, 2008)

Hi B Thomas,


> Do you have any idea why MS says 1,024 in a text cell?


Not really, though, as with the 255 character limit, I could think of reasons why the limit _might _exist.


----------

