This blog post explains the function list separator used in Excel functions.
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.
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 generally 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.
By default, Excel uses the list separator defined under regional settings in Control Panel. The US English version of Excel uses a comma (,) for list separator by default. international versions may use a semicolon (;). These variables are country dependent.
This will effect how functions are entered in Excel and the result.
Note: Excel automatically translates the separator in many cases. Formula error with wrong separator
If you try to enter a formula with the wrong list separator, you’ll get an error that says “There’s a problem with this formula”. There is nothing in this dialog that says anything about the list separators
This is an especially common problem if you are in another region and copy and paste an example formula from a US website.
The best solution is to change the list separator used in your region or country.
Regional settings for list separator
Windows setting:
- Control Panel à Region à Additional settings à Number tab
- Change the list separator
- Also, check the setting for the correct decimal symbol. You may need to change this.
Tip: Inserting an Apostrophe
Its also not a good idea to insert an apostrophe in front of a number, for instance a mobile number which starts with a zero.
If you are using a count function you will not get the correct answer, Excel will count the apostrophe as a digit.
The best option is to change the number setting to text. After all, mobile or postal code numbers are not calculating numbers.
