Cell displays ################# instead of the text

[Rahul]

Hi,

I have run into an issue that when the text in the cell is greater than a
certain number of characters, it is displayed as "#####################". If
I change the cell type to "General" instead of "Text" then it displays fine.
How can I fix this so that the type can be "Text" and still display
properly.

Thanks
Rahul

 

[Dave Peterson]

Try changing the cell's format from Text to General (or anything but text).

 

[Peo Sjoblom]

I have never seen that?

 

[Rahul]

That works but I am populating a list programatically and if there is data
that is more than 256 and less that 1024 characters then the list looses any
formatting that the user may have done

 

[Gord Dibben]

Then it is up to you to programatically format the cells to General when you
populate.

If you need help with that post the code you are currently using to populate the
cells.


Gord Dibben MS Excel MVP

That works but I am populating a list programatically and if there is data
that is more than 256 and less that 1024 characters then the list looses any
formatting that the user may have done

 

[Dave Peterson]

I've seen it seem to truncate characters when you get close to 1000
characters--but not at 255.

The workaround for that is to put alt-enters every 80-100 characters.

 

[Dave Peterson]

What kind of formatting is lost?

That works but I am populating a list programatically and if there is data
that is more than 256 and less that 1024 characters then the list looses any
formatting that the user may have done

 

[Dave Peterson]

If you keep the format the cell General, excel will do what it wants--including
scientific notation.

But you have alternatives...

#1. Give the cell a Number format (with 0 decimal places, if you want)
#2. Give the cell a custom number format like: 000000000000
(12 digits with leading 0's)
#3. Treat the cell as text
a. Precede your entry with an apostrophe: '001234
b. Preformat your cell as Text

Both #3 options will treat your entry as text. Arithmetic functions may not
work the way you want.

And if you save your file as a text file (.txt or .csv), you'll see the leading
0's in the text file (using notepad), but you'll have to be careful if you
reopen that text file in excel.

 

[Dave Peterson]

I think everyone can feel your pain.

Our part numbers were up to 2 characters, hyphen, 6 numbers, hyphen, up to 4
characters.

Some customers/vendors would jam all the characters together, dropping the
hyphens and losing the leading spaces/0's.

My eyeballs would hurt for weeks when trying to compare/contrast part numbers.