Excel - Cell displays ################# instead of the text

Asked By Rahul on 29-Aug-07 05:49 PM
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 replied on 29-Aug-07 08:04 PM
Try changing the cell's format from Text to General (or anything but text).



--

Dave Peterson
Andrew Rossmann replied on 30-Aug-07 05:29 PM
In article <46D60984.54B044B8@verizonXSPAM.net>,
petersod@verizonXSPAM.net says...

Problem is, General appears to truncate text that is longer than about
256 characters or so.

--
If there is a no_junk in my address, please REMOVE it before replying!
All junk mail senders will be prosecuted to the fullest extent of the
law!!
http://home.att.net/~andyross
Peo Sjoblom replied on 30-Aug-07 05:49 PM
I have never seen that?


--
Regards,

Peo Sjoblom
Rahul replied on 30-Aug-07 06:37 PM
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 replied on 30-Aug-07 07:00 PM
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
Dave Peterson replied on 30-Aug-07 07:32 PM
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
Dave Peterson replied on 30-Aug-07 07:32 PM
What kind of formatting is lost?


--

Dave Peterson
Andrew Rossmann replied on 31-Aug-07 11:35 AM
In article <46D75371.6469DB53@verizonXSPAM.net>,
petersod@verizonXSPAM.net says...

Another issue is that it can mangle numbers. I often read in Bill-Of-
Material style data that can contain part numbers that are 12-digit
numbers:
232270412345
Using General, it displays in exponential format:
2.3227E+11
Worse, if I export in some formats, it outputs as exponential, making
it useless as I lose resolution. Reading the above will give:
232270000000
Also, part numbers may have one or more leading zeroes, which are
dropped and completely lost.

--
If there is a no_junk in my address, please REMOVE it before replying!
All junk mail senders will be prosecuted to the fullest extent of the
law!!
http://home.att.net/~andyross
Dave Peterson replied on 31-Aug-07 12:02 PM
If you keep the format the cell General, excel will do what it wants--including
scientific notation.

But you have alternatives...

(12 digits with leading 0's)
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
Andrew Rossmann replied on 31-Aug-07 12:54 PM
In article <46D83B78.67FCFEDF@verizonXSPAM.net>,
petersod@verizonXSPAM.net says...

That is the main issue I tend to have. I often import text or CSV files
generated by others. I've set up Excel to always confirm import
conversions, and generally set all columns to text. I can always change
it if needed. That manufacturer part number can be many types. Not just
12NC, but various forms of text, too.

The other problem I have is getting binary Excel files, but somebody
imported without setting the proper import settings, leaving me with a
mess.

--
If there is a no_junk in my address, please REMOVE it before replying!
All junk mail senders will be prosecuted to the fullest extent of the
law!!
http://home.att.net/~andyross
Dave Peterson replied on 31-Aug-07 02:45 PM
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.


--

Dave Peterson