Instructions:

List separator conflict in regional settings 

1.1. Introduction 

The following issue can affect IDEA®, please see the solution below if you get a syntax error or list separator error in IDEA®: 

1.2 Background of the issue 

If the list separator in Windows regional settings is the same character as the decimal symbol on your computer, this will cause an error in IDEA® when using functions in an equation. 

A list separator is used to separate parameters used in functions, for example, as they are used in Excel. This is the same in IDEA® and are dependent on your regional settings. 

The most common separators used are a comma (“,”) or a semicolon (“;”). It is important to only use one of these options. 

If your regional settings are different to your settings in Excel, this will cause an error in IDEA® such as “invalid syntax” or “bad equation used”. 

1.3 How to fix the issue 

To test if the list separators are working, you can either test this in IDEA® or Excel. 

1. To test if the list separators are working, you can try use a function in IDEA® software, in the equation editor, if you are familiar with IDEA® 

2. Otherwise, we first need to check which list separator is used in Excel, for this example: 

3. Open a blank workbook in Excel 

4. Create the following formula 

5. The formula used is CONCATENATE(A1,B1) 

6. If no errors occur, then your list separator is a comma “,” 

7. If there is an error, then your separator is a semi colon “;” 

8. Try this formula instead: 

9. CONCATENATE(A1;B1) 

10. Once you have identified which list separator you are using, you will need to make sure your regional settings match this. 

11. To do this, click on the Windows button 

12. Once the windows menu pops up, type in “control panel” 

13. You should see this, and then select it to open it: 

14. Change view by: to category and click on “Change date, time or number formats” 

15. Once in the menu click on Additional settings 

16. Once in the next menu, check your list separator to see if it matches what is used in Excel. If it does not match, then it is likely that you have a conflict between the decimal symbol and the list separator. If it is different to what you saw in Excel and the decimal and list separator symbols are the same, these are the two options to fixing the issue: 

1. The most common use of a decimal symbol is a full stop “.” And the most common list separator is a comma “,”. If you want to use these values, please update the values in the selections shown below. Once you apply it, you will see that the list separators are then updated in Excel where formulas will now use the comma and not the semicolon. 

2. If you want a comma as your decimal point, then please use comma “,” as your decimal point and then make the list separator a semicolon “;” (so it matches what you found in Excel) 

17. change the settings to your preferred choice from the step above:  

18. Click apply and close these menus 

19. Final Step - You can try run the equation editor in IDEA® again and see if it successfully completes this time, using the value you chose for the list separator, either a comma or a semicolon, please do not use any other character. 

20. Done 

   Related Articles