 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Cell displays ################# instead of the text
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 Try changing the cell's format from Text to General (or anything but text). > Hi, > [quoted text clipped - 6 lines] > Thanks > Rahul  SignatureDave Peterson > Try changing the cell's format from Text to General (or anything but text). Problem is, General appears to truncate text that is longer than about 256 characters or so. SignatureIf 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 I have never seen that? SignatureRegards, Peo Sjoblom >> Try changing the cell's format from Text to General (or anything but >> text). > > Problem is, General appears to truncate text that is longer than about > 256 characters or so. 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. > > Try changing the cell's format from Text to General (or anything but text). > [quoted text clipped - 6 lines] > law!! > http://home.att.net/~andyross SignatureDave 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. 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. SignatureIf 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 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. > > I've seen it seem to truncate characters when you get close to 1000 > > characters--but not at 255. [quoted text clipped - 18 lines] > law!! > http://home.att.net/~andyross SignatureDave Peterson > If you keep the format the cell General, excel will do what it wants--including > scientific notation. [quoted text clipped - 14 lines] > 0's in the text file (using notepad), but you'll have to be careful if you > reopen that text file in excel. 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. SignatureIf 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 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. > 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 [quoted text clipped - 11 lines] > law!! > http://home.att.net/~andyross SignatureDave Peterson 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 > Try changing the cell's format from Text to General (or anything but > text). [quoted text clipped - 11 lines] >> Thanks >> Rahul 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 [quoted text clipped - 15 lines] >>> Thanks >>> Rahul 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 [quoted text clipped - 19 lines] > > > > Dave Peterson SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.