How to Fix Excel Decimal Separators and Comma Values on Mac
Question details
The user needs to change the decimal separator in Excel on a Mac from a comma to a period so that imported financial data, such as UK bank statements, can be calculated correctly.
- Product
- Microsoft Excel
- Device & OS
- macOS
- Scenario
- Importing or pasting numerical data from foreign formats (e.g., UK bank statements) where the decimal point conflicts with local system settings.
- Observed behavior
- Imported numbers containing periods are treated as text or formatted incorrectly, preventing normal mathematical operations like summing.
Ensure you have saved and closed all active Excel workbooks so the new regional formatting can be applied properly when you reopen the application.
Change macOS Regional Settings for Decimal Separators
Adjust your Mac's system-level Language & Region settings to force Excel to use periods for decimals instead of commas.
Unlike the Windows version of Excel, Excel for Mac does not have a built-in feature to override the decimal and thousands separators independently. Instead, it relies entirely on your macOS system preferences.
By modifying the advanced number formats in your system settings, you can ensure that Excel correctly interprets periods as decimals for your imported UK bank statements.
Click the Apple menu icon in the top-left corner of your screen and select 'System Settings' (or 'System Preferences' on older macOS versions).
Click on 'General' in the left sidebar, then select 'Language & Region' on the right side.
Look for the 'Number Format' section. On older macOS versions, you may need to click an 'Advanced' button to access these settings.
Set the 'Decimal separator' to a period (.) and the 'Grouping separator' (or list separator) to a comma (,).
Close System Settings, restart Microsoft Excel, and reimport or paste your bank statement values. They will now be recognized correctly as calculable numbers.
Import and Format Financial Data Easily in WPS Spreadsheets
WPS Spreadsheets allows you to specify custom decimal and thousand separators directly during the data import process. This means you don't have to alter your entire Mac's regional settings just to fix one bank statement.
- 1. Open Your File: Launch WPS Spreadsheets and open your bank statement or CSV file.
- 2. Select the Data: Highlight the column containing the incorrectly formatted imported amounts.
- 3. Use Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.
- 4. Configure Advanced Separators: Proceed to Step 3 of the wizard, click 'Advanced', and set the Decimal separator to a period (.) and the Thousands separator to a comma (,).
- 5. Apply and Sum: Click 'Finish' to apply the formatting. Your text will instantly convert to numbers, allowing you to sum them accurately.
Frequently Asked Questions
Why does Excel on Mac change periods to commas in my bank statements?
Excel for Mac defaults to your macOS system's regional settings. If your Mac is set to a region that uses commas for decimals (such as many European countries), Excel will automatically apply that format to your imported data.
Can I change the decimal separator only in Excel without affecting my Mac?
No. Unlike the Windows version of Excel, Excel for Mac does not have a built-in option in its Preferences to override the system-level decimal separators. You must change the macOS Language & Region settings.
How do I fix numbers that are stored as text after importing?
After correcting your system's decimal separator, you can use the 'Text to Columns' feature under the Data tab, or multiply the entire column by 1 using 'Paste Special', to convert the text strings back into calculable numbers.
What if I need to switch back to my original region settings later?
You can freely toggle your macOS decimal separator back to a comma in the Language & Region settings after you finish processing your bank statement. Excel will update its display format the next time you open the application.
