LosOjos Posted June 7, 2011 Posted June 7, 2011 (edited) Really confusing me this one, it's quite specific but I'm hoping there's a fix. If you have a cell containing a long chunk of text (appears to be over 250 characters), in Excel it will display as a line of pounds ('#'), until you click in to the cell at which point you can see the actual text. If you copy these values and try to paste them as text in another spreadsheet (via paste special), rather than copy the actual text, it copies a line of pounds instead. Anyone know how to fix this? If it helps, my situation is trying to transfer reports from XLS spreadsheets in to formatted XML sheets for SIMS import (don't ask!) Edited June 7, 2011 by LosOjos
cromertech Posted June 7, 2011 Posted June 7, 2011 Try right clicking the cells and setting them to word wrap. You should see all the text in the cell s then and not the # correction: sorry 2007 the wrap text function is in the home tab 1
LosOjos Posted June 7, 2011 Author Posted June 7, 2011 Try right clicking the cells and setting them to word wrap. You should see all the text in the cell s then and not the # correction: sorry 2007 the wrap text function is in the home tab Thanks for the reply, that was my first thought too but with particularly long text, Excel will still display pounds even with word wrap enabled and the cell resized EDIT: and the problem persists (just thought I should be clear!)
cromertech Posted June 7, 2011 Posted June 7, 2011 look like you are out of luck. Maximum text field size is 255 characters as stated here Excel specifications and limits - Excel - Office.com Figures for 2010 but text field length has been the same since office xp maybe even earlier. You'll have to write less i think
LosOjos Posted June 7, 2011 Author Posted June 7, 2011 You'll have to write less i think That's what I keep telling them but some teachers here just seem to love writing essays! It plays havoc with my A4 template too!
cromertech Posted June 7, 2011 Posted June 7, 2011 I've just noticed this bit Total number of characters that a cell can contain: 32,767 characters but you can only display 255 of these in a cell. Does it do the same if you copy from the entry bar at the top or is this what you mean?
cromertech Posted June 7, 2011 Posted June 7, 2011 Looking at your original reason for needing this. Can you teach your staff to export the marksheets (I guess that's what we are doing) and edit them because then even if they save them in xls format you can still resave them as xml for import. We do it like this and although you always get the odd one who exports the wrong type it usually works ok.
LosOjos Posted June 7, 2011 Author Posted June 7, 2011 I've just noticed this bit but you can only display 255 of these in a cell. Does it do the same if you copy from the entry bar at the top or is this what you mean? It does work this way, but I was hoping there'd be a less time consuming way of doing it Looking at your original reason for needing this. Can you teach your staff to export the marksheets (I guess that's what we are doing) and edit them because then even if they save them in xls format you can still resave them as xml for import. We do it like this and although you always get the odd one who exports the wrong type it usually works ok. They were originally sent the XML straight from SIMS, which they've then sent me back saved as XLS, but if I try to save them as XML it doesn't work because the XML schema is (understandably) missing. If I copy & paste in to a freshly exported formatted marksheet, Excel crashes when saving, so I have to copy & paste as text in order to save the XML file. How do you save your XLS as XML? Do you attach the XML schema somehow?
mac_shinobi Posted June 7, 2011 Posted June 7, 2011 If you format a cell or the cells as custom or special or anything like that , does that make any difference ?
LosOjos Posted June 7, 2011 Author Posted June 7, 2011 If you format a cell or the cells as custom or special or anything like that , does that make any difference ? It won't open the format dialog Looks like I have a busted install...
mac_shinobi Posted June 7, 2011 Posted June 7, 2011 It won't open the format dialog Looks like I have a busted install... remove and do a complete install instead of a typical install ?
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now