Fix Excel Separators Not Changing After Disabling System Settings
Question details
Users are unable to see changes in their decimal and thousands separators after modifying Excel settings to disable system separators.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to customize decimal and thousands separators for number formatting within a workbook.
- Observed behavior
- The expected custom separator characters are not applied to the numerical data, often due to cells being formatted as text or interference from legacy compatibility settings.
Before modifying advanced settings, ensure that your workbook is not locked and that you have restarted the application after making initial separator changes to see if they apply.
Verify and Convert Text to Numbers
Use this solution to check if your numbers are being treated as text strings, which prevents custom formatting from being applied.
When data is imported or copied from external sources, numbers are often stored as text. Excel cannot apply custom decimal or thousands separators to text values.
Select an empty cell next to your data and type =ISTEXT(A1) (replace A1 with your target cell). If it returns TRUE, the number is stored as text.
In another empty cell, type =ISNUMBER(--A1). If this returns TRUE, Excel can successfully convert the text to a numerical value.
Select the column of text numbers, go to the Data tab, click 'Text to Columns', and click 'Finish' without changing any settings to convert them into standard numbers.
Disable Lotus Compatibility Options
Use this solution if your workbook has inherited legacy Lotus 1-2-3 settings that override standard Excel number formatting behaviors.
Seamlessly Manage Number Formats with WPS Spreadsheet
If you frequently encounter formatting issues, WPS Spreadsheet offers an intuitive, highly compatible environment for managing your data. It perfectly handles custom decimal and thousands separators, ensuring your data is always formatted exactly the way you need it.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to change the number separators.
- 2. Access Options: Click the 'Menu' button in the top left corner and select 'Options' from the drop-down list.
- 3. Modify Separator Settings: Navigate to the 'Advanced' tab. Uncheck 'Use system separators', enter your preferred characters for decimal and thousands separators, and click 'OK'.
Frequently Asked Questions
Why are my Excel numbers showing up as text?
This usually happens when data is imported from a CSV file, copied from a web page, or generated by other software. The data carries text formatting properties, preventing standard number formats like decimal separators from applying correctly.
What are system separators in spreadsheet software?
System separators are the default characters used for decimals and thousands in numbers. They are defined by your computer's regional operating system settings (for example, using a comma instead of a period for decimals in many European countries).
Does disabling system separators affect other programs?
No. When you uncheck 'Use system separators' within Excel or WPS Spreadsheet Options, you are only changing how that specific application displays numbers. Your Windows or Mac system-wide settings remain unchanged.
