- Sep 4, 2006
This worked, thanks then I converted the "text - number" in error on formula working perfectFor some as yet obscure reason, Microsoft has changed the decimal indicator from being a full-stop & the list separator as being a comma in Windows 7, when the regional settings are set to South Africa, to instead reflect as a comma for the decimal indicator & either a semi-colon (or a comma as well) as the list separator, when using either Windows 8.x or Windows 10.
This throws out calculations when using data compiled in Excel on a Windows 7 PC using a full-stop, and using that same spreadsheet in Excel on a Windows 8.x or Windows 10 PC, where it expects a comma as the decimal separator.
It may also affect CSV data import as well, as Excel in Windows 7 used a comma, whereas Excel in Windows 8.x or Windows 10 may be expecting a semi-colon as the CSV list separator.
Here's how you solve this issue - you may have to do this on ALL PC's in your environment that are on Windows 10, so that the correct symbols (full-stop for decimal, comma for list separator) are recognised.
First make sure that any MS Office programs - Excel, Word, Outlook, etc are all closed as this change will affect their global settings, once you have followed the procedure below.
You can re-open Excel and from now on it will now recognise the full-stop as the decimal & the comma as the CSV list separator.
- Go to Settings in Windows 10 (I am using version 1903 for these steps) - maximise the Settings Window so you can see all the menus that follow on both the left & right screen panels
- Click on Time & Language
- At the top right - under Related Settings, click on Date, time & regional formatting
- When the Region screen appears, again go to Related Settings at the top right, and click on Additional Date, time & regional settings
- A new Control Panel pop-up window will appear for Clock & Region settings
- Under the second option - Region - click on Change date, time or number formats
- Yet another Control Panel pop-up window for Region will appear
- Make sure Format at the top is set to English (South Africa)
Then click on the Additional Settings button at the bottom of the Region window
A new pop-up window for Customise format will appear
- The two options you need to change are as follows:
Delete the comma symbol in the Decimal symbol option and type in a full-stop
- Make sure that the entry in the List separator option is a comma and NOT a semi-colon (change if necessary)
Click the Apply button and then the OK button - the Customize format pop-up will close
Click the OK button of the Region pop-up window - it will also close
- Close the Control Panel window for Clock & Region
Close the main Windows 10 Settings window