Formula errors in Excel when list separater is not set correctly

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

Symptoms
When entering a formula an error will occur if trying to use a symbol that is not the default 'list separator' in the Windows Regional settings.

"We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it, or click Help for more info on common formula problems."


Cause
If the error occurs when you use a character to separate the arguments you expect to work and Excel will not accept it then this is typically caused by either or both of the following scenarios:
  • The list separator in Windows - Regional Settings does not match what is being typed for the Excel formula.
  • The 'Use system separators' option is set in Excel Advanced Options and does not match what is being typed for the Excel formula.

Resolution
To workaround the error message follow the following steps:

1. Determine if 'Use system separators' is selected in Excel options. From the File menu select Options. Select Advanced. In the 'Editing options' group determine if 'Use system separators' is unselected. If it is unselected you can select it to have Excel use the Windows Regional settings or specify the 'Thousands separator' you want to use.

If the 'Use system separators' is enabled then look in the Region (Regional) Settings in the Windows Control Panel. The list separator is specified in the Additional settings.

2. Use the same character as the designated list separator in Windows settings when creating the formula(s).
3. Use the Reset button to reset the options to match the default for the selected country or modify the Windows List Separator to a different character (some special characters cannot be used).
Note: By default, Microsoft Excel uses the system separators that are defined in the regional settings in Control Panel. If you sometimes need to display numbers with different separators for thousands or decimals, you can temporarily replace the system separators with custom separators. To do that please follow the steps in the following article http://office.microsoft.com/en-us/excel-help/change-the-separator-for-thousands-and-decimals-HP003089676.aspx

Note: The comma is the default list separator for US - English Locale.


More information
Changing the List separator in the Windows Region settings will affect the delimiter used when opening or saving a Comma-separated value (.csv) file as Excel utilizes the Windows list separator character for the delimiter in .csv files.

Regional Setting formula error errmsg list separator seperator
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 2853427 - Last Review: 09/23/2015 08:57:00 - Revision: 3.0

Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Excel 2016

  • KB2853427
Feedback