'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Cannot Change the Thousands Separator for CSV Files
Question details
The user needs to change the thousands separator for CSV files in Excel without altering Windows regional settings, but Excel rejects the input with an error stating the entry may contain no more than one character.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Attempting to customize data formatting and separators directly within Excel to properly view or save CSV files.
- Observed behavior
- Excel displays an error message stating that the separator entry may contain no more than one character, even when the user believes they have only typed a single character.
Verify that your Num Lock key is turned off on your keyboard, as it can sometimes input hidden control characters into the settings fields without your knowledge.
Clear Hidden Characters in the Separator Settings
This method removes invisible characters that trigger the length error, allowing you to successfully change the thousands separator.
Often, invisible spaces or control characters accidentally get pasted or typed into the thousands separator field. Because the field expects exactly one character, these hidden characters cause Excel to throw an error. Navigating via the keyboard ensures you overwrite the field entirely.
Launch Microsoft Excel, click on the 'File' menu, select 'Options', and then click on 'Advanced' in the left-hand navigation pane.
Scroll to the 'Editing options' section and uncheck the box that says 'Use system separators' to enable manual input for decimals and thousands.
Ensure your keyboard's Num Lock is turned off. Click inside the 'Decimal separator' field, then press the 'Tab' key on your keyboard to move the cursor into the 'Thousands separator' field.
Once focused on the 'Thousands separator' field, press the 'Delete' key to wipe out any invisible characters or hidden spaces.
Type your desired single character for the thousands separator, click 'OK' at the bottom of the window to save the changes, and reopen your CSV file to test.
Easily Manage CSV Separators with WPS Spreadsheet
WPS Spreadsheet provides an intuitive and bug-free environment for customizing data formats. You can effortlessly adjust decimal and thousands separators for CSV files without dealing with hidden character errors.
- 1. Launch WPS Spreadsheet: Open WPS Office and start the WPS Spreadsheet application.
- 2. Access Options: Click on the 'Menu' button in the top-left corner and select 'Options' from the drop-down list.
- 3. Modify Edit Settings: Navigate to the 'Edit' tab within the Options dialog. Locate and uncheck the 'Use system separators' option.
- 4. Set Custom Separators: Input your preferred characters into the 'Decimal separator' and 'Thousands separator' fields.
- 5. Apply Changes: Click 'OK' to save your settings. Your CSV files will now display using the custom separators.
Frequently Asked Questions
Why does Excel say my separator has more than one character when I only typed one?
This happens when hidden control characters, such as spaces or invisible formatting codes, are accidentally inserted into the text field. Pressing Delete or Backspace while the field is fully selected clears these invisible characters.
Will changing the thousands separator in Excel affect other programs on my Windows PC?
No. By unchecking 'Use system separators' in Excel's Advanced Options, you isolate the change strictly to Excel. It overrides the default Windows regional settings without modifying the system-wide Control Panel settings.
Can I use a space as a thousands separator in Excel?
Yes, you can use a single space as a thousands separator. Follow the same steps in the Advanced settings and press the spacebar once in the 'Thousands separator' field after ensuring it is completely clear of other characters.
