+ Post New Thread
Results 1 to 11 of 11
Office Software Thread, Excel - Long text cells copy & paste as '#' in Technical; Really confusing me this one, it's quite specific but I'm hoping there's a fix. If you have a cell containing ...
  1. #1

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813

    Excel - Long text cells copy & paste as '#'

    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!)
    Last edited by LosOjos; 7th June 2011 at 04:31 PM.

  2. #2
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    55
    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

  3. Thanks to cromertech from:

    LosOjos (7th June 2011)

  4. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by cromertech View Post
    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!)

  5. #4
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    55
    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

  6. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by cromertech View Post
    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!

  7. #6
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    55
    I've just noticed this bit
    Quote Originally Posted by Microsoft
    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?

  8. #7
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    55
    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.

  9. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by cromertech View Post
    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

    Quote Originally Posted by cromertech View Post
    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?

  10. #9

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    10,052
    Thank Post
    3,583
    Thanked 1,123 Times in 1,025 Posts
    Rep Power
    377
    If you format a cell or the cells as custom or special or anything like that , does that make any difference ?

  11. #10

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by mac_shinobi View Post
    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...

  12. #11

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    10,052
    Thank Post
    3,583
    Thanked 1,123 Times in 1,025 Posts
    Rep Power
    377
    Quote Originally Posted by LosOjos View Post
    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 ?



SHARE:
+ Post New Thread

Similar Threads

  1. android phone 2.1 copy and paste.
    By chazzy2501 in forum General Chat
    Replies: 5
    Last Post: 12th May 2011, 02:52 PM
  2. [MS Office - 2007] Excel - Choosing Two Cells
    By garethedmondson in forum Office Software
    Replies: 2
    Last Post: 20th October 2010, 05:24 PM
  3. Copy and Paste in Hyper-V
    By FN-GM in forum Windows Server 2008 R2
    Replies: 2
    Last Post: 30th November 2009, 10:35 PM
  4. preventing copy and paste
    By mikeymike in forum Windows
    Replies: 0
    Last Post: 6th October 2008, 11:19 AM
  5. Excel Not Recognizing Cells of Equal Value
    By G_Money in forum Windows
    Replies: 2
    Last Post: 21st June 2007, 09:33 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •