MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Commas Stop Working in Formulas


July 06, 2018 - by Bill Jelen

Commas Stop Working in Formulas

A bizarre problem today: Suddenly, Excel formulas will no longer accept a comma as the argument separator. You start to type =VLOOKUP(A2, when you use the mouse or arrow keys to select the lookup table, Excel beeps with "There is a problem with this formula." Yes! Of course there is a problem with this formula... I haven't finished typing the formula yet.

Why is there a problem with this formula?
Why is there a problem with this formula?

There is an interesting clue that appears in the tooltip while you are typing the formula. Instead of commas between arguments, the tooltip is showing vertical bar characters. Programmers call these symbols a "Pipe". It looks like this: |

The tooltip shows a pipe where the commas belong
The tooltip shows a pipe where the commas belong

I learned of this problem from a person in my Huntsville Alabama live Power Excel seminar. That clever person tried building the VLOOKUP using the Function Arguments dialog. To try this, type =VLOOKUP( and then press Ctrl + A. Fill in the boxes in Function Arguments and then click OK. The VLOOKUP formula will work, but it shows =VLOOKUP(A2|$F$2:$G$13|2|False).

Why does Excel now use a | instead of a comma?
Why does Excel now use a | instead of a comma?
To make matters worse, this person was using a small laptop keyboard that did not offer a pipe!

I asked my MVP friends and the folks in Europe were familiar with the List Separator setting in the Windows Control Panel. In some countries, the comma and decimal point is reversed. $1,234.56 would be written as $1.234,56. If your decimal separator is a comma, then Excel will use a semi-colon as the list separator.

Depending on your version of Windows, navigate to:

  • Windows 7 > Control Panel > Regional Settings > Additional Settings > List Separator
  • Windows 10 > Control Panel > Clock, Language and Region > Region: Change date, time or number formats > Additional Settings > Numbers > List Separator

Choose Additional Settings
Choose Additional Settings
Change the List Separator back to a comma
Change the List Separator back to a comma

The bigger question: Who changed the list separator from a comma to a pipe? I've found some articles on the web where the person has a CSV file that does not use commas. Instead of a comma, the data is separated by a pipe. But the file type still says .CSV.

This
This "CSV" file uses pipe instead of comma

The well-intentioned article suggested changing the list separator from a comma to a pipe so you could open this file by double-clicking. That is great advice if (a) you never enter any formulas in Excel and (b) you never open any CSV files with a comma as the list separator.

A far better solution is this one suggested by Excel MVP Jan-Karel Pietriese. Alternatively, change the file extension from .CSV to .TXT. When you open the file in Excel, you can specify the delimiter as a pipe in the second step of the Text Import Wizard.

If you are reading this page because your list separator changed, please log a comment in the YouTube video below letting me know if you recall changing the list separator or if some other rogue program changed the setting without your knowledge.

Watch Video

Download Excel File

To download the excel file: commas-stop-working-in-formulas.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Every spreadsheet tells a story"

Title Photo: Aaron Burden on Unsplash