Text in Text-formatted cells changes to ### after the 255th charac

[wildetudor]

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.
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.

 

[Ragdyer]

It is a bug.

AFAIK, the only way to revert back to text, without changing the format to
General, is to have the character count *exceed 1024*.

YES ! ... the text returns after 1024 characters, so the actual bug is
*between* 256 and 1024 characters *only*.

Would you want to try inserting spaces in appropriate places to attain that
1024 character count?

 

[Gord Dibben]

No way to get around formatting to General to avoid the ############## if
string is between 255 and 1024 characters.

If you are not preceding the text strings with an = sign I don't know why a
- sign or "not" would make Excel think you are typing a formula.

Unless you have Lotus Transition settings enabled..............that would
throw a #NAME? error if you typed "-qwerty"


Gord Dibben MS Excel MVP