convert comma to decimal point in excel

Discussion in 'Mac Apps and Mac App Store' started by userjoy, Feb 9, 2013.

  1. macrumors member

    Jan 1, 2012
    [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?
  2. macrumors G4

    May 10, 2004
    You can customise the formats in those system settings.
  3. macrumors G4

    May 10, 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
  4. thread starter macrumors member

    Jan 1, 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.
  5. macrumors 68040


    Dec 5, 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.
  6. macrumors 68000

    Mar 30, 2011
    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.

Share This Page