Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Reply
 
Thread Tools Search this Thread Display Modes
Old Feb 9, 2013, 06:58 AM   #1
userjoy
macrumors newbie
 
Join Date: Jan 2012
convert comma to decimal point in excel

[Using MacBook Air, OSX 10.7.5, MS Excel version 14 (100825)]

My mac is set to American standards - uses periods to indicate decimals, commas for thousands separators, etc.

I've just received a European excel file using commas for decimals and I'm trying to figure out how to change it to periods for decimals.

If I go to system preferences - language and text - formats - numbers, it shows the standard US preferences, so I think the solution is in excel.

Excel 14 does not have a "tools-options" command.

Format - cells - accounting in Excel does not often an option to make that change, it simply shows the commas as the default for this spreadsheet.

Excel help says:

"Date, time, and number formats
When you enter dates, times, and numbers, Office displays them based on your settings in Mac OS X Language & Text preferences."

That is true when I create files, but when I'm using a file created using other formats, my settings in OS X Language and Text preferences do not override the settings used to create that file.

I can't find anything in Excel preferences that lets me change the format of a file when it differs from my defaults.

Does anyone know how to do this?
userjoy is offline   0 Reply With Quote
Old Feb 9, 2013, 07:01 AM   #2
cube
macrumors G3
 
Join Date: May 2004
You can customise the formats in those system settings.
cube is offline   0 Reply With Quote
Old Feb 9, 2013, 07:14 AM   #3
cube
macrumors G3
 
Join Date: May 2004
.csv is not an Excel format, it is a simple generic format.

You change the system format to the one of the file.
You start the program and import the file.
You save the file in a proprietary Excel format.
Now that proprietary file displays in the region of the computer.

If you want to change the .csv format:

Set the target format in system settings
Start the program and open the propietary file.
Export as .csv
cube is offline   0 Reply With Quote
Old Feb 9, 2013, 07:16 AM   #4
userjoy
Thread Starter
macrumors newbie
 
Join Date: Jan 2012
In the Mac system settings? Yes, but I don't need to - my Mac settings are what they want them to be (US standard). But they don't override the defaults of the Excel file I've been given.

Interestingly, when I do calculations in the Excel file, they follow the formats as set in my Mac preferences. But the values from the original data in the spreadsheets retain the European formats. I could probably play some games with it, doing something like multiplying everything by 1 and then copying formulas to values, but that won't always be a viable solution to this problem. I assume there has to be a standard way to tell it to convert from European formats to North American formats.
userjoy is offline   0 Reply With Quote
Old Feb 14, 2013, 05:51 AM   #5
dusk007
macrumors 68030
 
Join Date: Dec 2009
I think csv is just a file format for exports. It is basically just a text file.
If you go file > import > csv file. You should be able to define what kind of commas it is using and how to interpret it.
The excel file later should be indifferent towards that and just display it however you want.
__________________
15" MBP 2013 2.3/750M, 16GB/512GB
dusk007 is offline   0 Reply With Quote
Old Feb 14, 2013, 06:54 AM   #6
marc11
macrumors 68000
 
Join Date: Mar 2011
Location: NY USA
Highlight the column of text and change the format from number to general text. Then find a replace on the highlighted column to change any period to a comma.
__________________
Various Apple Products
marc11 is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
iPad: Exclamation point instead of comma when I type flyersgl iPad 1 Dec 28, 2013 03:01 AM
Convert 3D point from 1 coordinate system to other namanhams iPhone/iPad Programming 8 Jul 27, 2012 11:14 AM
initWithFormat won't use locale for decimal point RLScott iPhone/iPad Programming 3 Jul 17, 2012 02:00 PM
Removing comma from a list fhill2 Mac Programming 8 Jun 25, 2012 12:07 PM
I need a way to convert old Excel files GilDawson Mac Basics and Help 2 Jun 15, 2012 11:22 PM

Forum Jump

All times are GMT -5. The time now is 11:43 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC