Commas Stop Working in Formulas
July 06, 2018 - by Bill Jelen
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.
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: |
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
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
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.
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.
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