MS Excel problem...

Discussion in 'CycleChat Cafe' started by Arch, 30 Sep 2008.

  1. Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    Help!

    It's going to drive me nuts. I have data in SPSS. For various reasons, I needed to copy the SPSS sheets over to Excel to put some data from different files together. One of my SPSS variables is "Century" - ie, 12th, 15th, or, and this is the problem, in some cases a century range, ie, 12th-13th (entered as 12-13, as a string variable). The problem is, when I copy it over to excel, it's assuming 12-13 means December the 13th! (Dec-13). Clear?

    I've tried right click and format cell (ie, change from date to number, or general), but whatever I do (and I'm flailing about a bit) it just returns to Dec-13 wherever I type 12-13. It's doing the same with 9-10 (Sept-10) and so on, but 15-16 is fine, I guess it can't work out a month...

    What should I be doing, in terms of formating the cell? I think I must be missing something...

    Also, I tried a find and replace for the Dec-13 thing, and it couldn't find any!

    <bangs head on table>
     
  2. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    I think I've tried that.... But I'll try again, TBH, I've been trying random stuff so I'm not sure what I have done.....:biggrin:
     
  3. Crackle

    Crackle Squatter

    format the column to text and re-paste
     
  4. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    ok, will try, but I think I tried that too!:biggrin:
     
  5. Crackle

    Crackle Squatter

    I typed my re-paste bit after then.

    Clear the column, format to text, then re-paste the values. It should work I just tried it.
     
  6. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK

    Ok. Will try, and report back. Thanks all!

    Of course, it might be my laptop having a wobbly...!
     
  7. Brains

    Brains Guru

    Location:
    Greenwich
    Other option is to use a different seperator, rather than have the field read "12-13" make it "12C-13C" or "12th to 13th"

    The issue is Excel is designed to read certain number, hyphen or oblique, number formats as dates
     
  8. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    yeah, I could - trouble is the data is all set up already, so it would be tedious to change it all (esp if it won't even do find/replace!)

    Damn computers...:biggrin:
     
  9. bikepete

    bikepete Veteran

    Location:
    York, UK
    Go to the 'tools' menu and select 'options'. In the dialogue box, select the 'edit' tab. Turn off anything with the words 'automatic' or 'auto' in the name. Try again :-)
     
  10. Chuffy

    Chuffy Veteran

    HAH! Right then...<cracks knuckles, puts on Excel spannering trousers>

    Copy the naughty column in SPSS.

    Go to Excel, right click on the top cell of a column and select Paste Special. Select Paste As Text. Should sort the problem....


    If that doesn't work, PM me and I'll give you my work e-mail. I use SPSS at work and it shouldn't be too hard to figure out with a bit of time and some light brutality if you send me the .SAV file
     
  11. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    Cheers all. I'm tied up today teaching (well, teaching for an hour at lunchtime, but as it's a bunch of 16 year olds, my colleagues and I intend to spend the afternoon in the pub:biggrin:), but I'll print this thread out and try it all tomorrow...

    Annoying thing is, I've already done a load of copying - ideally, I'd want a way to convert what's already there. (the blip wasn't apparent on the first few documents...)

    Bloody computers. Still, without them, my project would take about 100 years, with slide rules and abaci....
     
  12. Chuffy

    Chuffy Veteran

    "abaci..." good grief, you academics and your correct plurals...xx(

    Hokay, I've had a little play and here's what you do...

    In Excel, Format the column that you want to paste the data into as Text.
    Copy the data column in SPSS (it is a String variable isn't it?)
    Right click in the Excel data column and select Paste Special. Select Text from the options it gives you.
    Bingo!

    Alternatively you can always save the SPSS file as an Excel file (Save As and select the appropriate file type from the drop down) which seems to keep the formatting. Excel will grumble ("Number stored as text" and lots of little green tabs in the cell corners) but it will work.

    Have fun down the pub....
     
  13. red_tom

    red_tom New Member

    Location:
    East London
    For the ones that have already converted themselves into dates do the following.

    Highlight the data, right click, format cells, custom, then type the following into the box underneath where it says Type:

    mm"th"-yy"th"

    Click ok and this should change it back to 12th-13th or whatever. Note that excel is still storing the data as a date but at least it's beign displayed correctly.
     
  14. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    Cheers.xx(

    Looks like I'll have to do a bit of re-pasting then, but that seems par for the course. I never had this trouble before (before I had the time off, and before my laptop went tits up and before I had to reload with the latest SPSS), so I wonder if it's a glitch between the newest SPSS and Excel...

    It's always the same with me and computers, I thrash about trying to do something and end up finding a way, but it's always longer winded than it need be....

    Still, at least I can operate the forum all rig....
     
  15. OP
    OP
    Arch

    Arch Married to Night Train

    Location:
    Salford, UK
    ..ht.

    Cheers redtom, that makes sense, I'll give it a go!
     
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice