longtalker

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

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

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

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

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

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 :p

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

Hi Ricco1962,

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

Glaswegian

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

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/ex...992911033.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...