How to Fix Incorrect Excel Number Formatting Like 1,/.-
Question details
Excel displays numerical values with unusual symbols, such as showing 1,/.- instead of 1000, even when the Currency or Number format is applied.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Applying number or currency formatting to cells containing numerical data.
- Observed behavior
- The numbers render as a malformed text string with incorrect decimal or thousands separators, rendering the data unreadable.
Before modifying system settings, verify that the affected cells actually contain numerical values rather than text by checking the formula bar. Numbers stored as text will not respond to standard formatting changes.
Reset Windows Regional Settings
Since Excel relies on Windows for its default decimal and thousands separator symbols, correcting the OS regional settings is the most effective way to resolve systemic formatting errors.
Incorrect or customized digit grouping and decimal symbols in the Windows Control Panel can cause Excel to render standard numbers as broken strings like '1,/.-'. Resetting these symbols to their default regional standards typically resolves the issue immediately.
Press the Windows key, type 'Control Panel' in the search bar, and press Enter.
Click on 'Clock and Region', then select 'Region' to open the Region dialog box.
In the Formats tab, click the 'Additional settings...' button at the bottom.
Under the Numbers tab, verify the 'Decimal symbol' (usually a period) and 'Digit grouping symbol' (usually a comma). Alternatively, click the 'Reset' button to restore the system defaults, then click 'OK'.
Close and reopen Microsoft Excel to apply the new system-wide regional settings to your spreadsheets.
Remove Invalid Custom Number Formats
If the issue only occurs in specific workbooks or cells, it may be caused by a corrupted or incorrectly typed custom number format code within Excel itself.
Format Numbers Accurately with WPS Spreadsheet
If you are tired of struggling with complicated formatting errors in Excel, try WPS Office. WPS Spreadsheet offers a highly intuitive formatting system that strictly controls cell data types to prevent visual glitches, all while maintaining perfect compatibility with your existing Excel files.
- 1. Open Your Spreadsheet: Launch WPS Office and open your workbook.
- 2. Select the Data: Highlight the cells containing the numbers you wish to format.
- 3. Access Format Options: Right-click and select 'Format Cells' from the context menu.
- 4. Apply Number Formatting: Choose the 'Number' category, easily set your decimal places, enable the 1000 separator, and click 'OK'.
Frequently Asked Questions
Why is my Excel cell showing a number as a weird date or text?
This happens when the cell is formatted as Text or Custom Date instead of General or Number. You can fix this by selecting the cell, pressing Ctrl+1 to open Format Cells, and choosing 'Number' or 'General' from the category list.
How do I change the comma to a decimal point in Excel?
You can change this either by adjusting your Windows Regional settings in the Control Panel, or within Excel by going to File > Options > Advanced. Uncheck 'Use system separators' and manually set your preferred decimal and thousands separators.
What does the format code #,##0.00 mean in Excel?
This is a standard custom number format. The hashtag (#) represents optional digits, the zero (0) ensures at least one digit is shown even if it's zero, the comma (,) adds a thousands separator, and the (.00) ensures the number always displays two decimal places.
Can I remove a custom number format I created by mistake?
Yes. Press Ctrl+1 to open the Format Cells dialog, go to the 'Custom' category under the Number tab, select the custom format you created in the list, and click the 'Delete' button below the list.
