|Feb 9, 2013, 06:58 AM||#1|
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?
|Feb 9, 2013, 07:14 AM||#3|
.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
|Feb 9, 2013, 07:16 AM||#4|
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.
|Feb 14, 2013, 05:51 AM||#5|
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
|Feb 14, 2013, 06:54 AM||#6|
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
|Thread Tools||Search this Thread|
|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|
All times are GMT -5. The time now is 11:43 PM.