MS Excel problem...

[Arch]

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>

 

[Arch]

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.....

 

[Crackle]

format the column to text and re-paste

 

[Arch]

format the column to text

ok, will try, but I think I tried that too!

 

[Crackle]

ok, will try, but I think I tried that too!

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.

 

[Arch]

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.

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

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

 

[Brains]

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

 

[Arch]

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...

 

[bikepete]

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 :-)

 

[Chuffy]

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

 

[Arch]

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), 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....

 

[Chuffy]

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

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....

 

[red_tom]

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.

 

[Arch]

Cheers.

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....

 

[Arch]

..ht.

Cheers redtom, that makes sense, I'll give it a go!