excel acting weird when replacing "," with "."

[Thormenting_Slumberjack]

I have a dataset with with commas before the decimals. I need to change them to punctuations because SPSS apparently doesn't read numbers with comma in them. When I replace the comma for for instance 6,5 I get a huge number (something like 40355 or something). It doesn't make sense to me. I've tried different number settings for the cells without any luck.. I don't want to change every number manually... Any idea?

 

[alansidman]

How are the cells formatted when presented as 6,5? Are they numbers or text? Are you using a European format? and wish to change to the US format? How do you have your decimal separator set globally?

Alan

 

[Thormenting_Slumberjack]

I think they were just "general" formatted. I don't know about the european and US format, and not sure with this global thing.. sorry I'm pretty green.... and european

 

[alansidman]

Go to Tools, Options, click on the International Tab. What is your decimal setting. How is your data set getting into Excel. Imported or data entry. I suspect that your international setting for decimals is set to ",". Change it to "." for this worksheet and see if that solves your issue.

Alan

 

[AMAS]

I agree with Alan that it seems to be because your Excel has European settings. I tried 6,5 in my North American formatted Excel and it won't recognize it at all. Once I change it 6.5 then it works fine.

As a last option, save your excel sheet (the active sheet) as a tab-deliniated text file, open it with Word and do a global replace of commas with periods.

AMAS

 

[Thormenting_Slumberjack]

Thank you both! I changed to US settings. That word trick was also pretty cool. It's all good now. Thanks for saving the day

- Henrik