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.
Learn Excel for MrExcel podcast, episode 2222 - Commas Stop Working in Formulas!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was in my Huntsville, Alabama seminar, walking around before the seminar to ask if anyone had any Excel questions, and Scott, he was ready for me, check this out, this was happening on Scott's computer. So I'm going to create a little VLOOKUP here, right? =VLOOKUP(A2, and I start to press the right arrow key to go over there to choose that table in column F, and it says “Hey, there's a problem in this formula.” Oh yeah, there's a problem with this one, I haven't finished the formula. Why do they think I've finished the formula, what's going on?
Alright, and so I'll close that, and Scott, I have to give Scott credit, Scott was brilliant here, he did this: =VLOOKUP, it was this little trick where you press Fx and it takes you into function arguments. So he built the formula using function arguments, which is great If you're new to formulas but, you know, you don't have to do this every single time, ,2,FALSE and he pressed OK. And then he looks at the formula, and instead of commas, he has what he calls a weird character. Well, you know, it's not a weird character, that's called a pipe, and it's used as the “OR” symbol in a lot of programming languages, and on my keyboard it's right above the Enter key. So there's a \, and then if you Shift that, there's a | character. But Scott, he was using some sort of a virtual system, his keyboard doesn't have a |, alright. So, now, holy smokes, he's in Excel where, instead of a comma, it's now a pipe and he doesn't have a pipe character on the keyboard. What is this, and why is this happening?
Alright so, I talked to the Excel team, and they're saying “Hey, yeah, we're seeing this happen increasingly, who's telling people to change the list separator?” And I found some websites where they say “Hey, you have a CSV file that's using a pipe instead of a comma” and they suggest going in to change the list separator to a pipe in the Control Panel. Oh, geez, you never want to change anything in the Control Panel because it has wide repercussions throughout all of your programs, including the list separators used in our formulas. So whoever wrote that website to change the list separator from a comma to a pipe must never entered formulas in their life, which is crazy!
Alright, so to get around this, instead of listening to THAT website, first thing is if you're getting a file that's CSV, rename it to File.txt and open in Excel. It'll walk you through the Text Import Wizard, and you can specify the delimiter in Step 2. OR go out and watch my video, episode 2087 - Prevent Scientific Notation on Import, which shows you a much better way to import CSV files, using the legacy "From CSV" that you can add up here to your Quick Access Toolbar, and then you can define the delimiter for one file only, instead of just globally changing the list separator.
Alright, so here's what we have to do, we have to go to Start, and then Control Panel. Now, I'm in Windows 7 on this machine, and it's a different Settings in Windows 10, I'll show you those. So I just search up here for Region and Language, “Change the date, time or number format” I have to go to Additional Settings, and sure enough, there is a List separator, and change that List separator from a pipe back to a comma, click OK, click OK. Now if you're on Windows 10, it’s Windows 10 Control Panel, Clock, Language and Region, Region, “Change date, time, or number formats”, Additional Settings, Numbers, List Separator - buried a lot deeper on Windows 10, a lot easier to get to in Windows 7.
Alright, now, let's try this again, see, already the commas are back to regular commas, and let's make sure that we can still enter things. So =VLOOKUP of this, comma, I guess it's already working, so that's awesome, ,2,FALSE, alright. What a weird one, comma stopped working in formulas, because somehow, the list separator got changed. Check out my book “MrExcel LIVe, The 54 Greatest Excel Tips of All Time”, Click that "i" on the top-right hand corner.
Alright, wrap-up: Today, comma stopped working in formulas in Excel, soon as you type a comma, move to the next argument, the formula gives you an error as if you're done with the formula. Clever, Scott in Huntsville, when it started happening to him, built the formula with a functions arguments dialog, you'll see =VLOOKUP, and it actually is there. The other thing you'll see is in the tool tip, those commas will be replaced by pipes. If you're in this weird state that character is a pipe, it's above the backslash on the US Keyboard, although Scott actually had no pipe on his keyboard. Go to Control Panel, Region & Language Settings, Additional Settings, List Separator, change list separator back to a comma. Now, if you changed the list separator because some system is giving you pipe-separated values instead of CSV, watch this video right here, there's the link, and that's down in the YouTube description so you can watch that. Just a better way to solve this, other than changing the list separator globally. To download the workbook from today's video, visit the URL in the YouTube description!
I want to thank Scott for bringing this to my attention at my Huntsville seminar, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Now hey, look, here's my ask for you: If you're at this video because this has happened to you, and you remember changing the list separator, can you go down to the YouTube comments and report back why you changed it? Who told you to change it? What was the circumstances that caused you to change it? If you're here at this video because this is happening to you, and you have no idea why it happened, or you didn't change it, there must be some program out there changing it. Again, hit the YouTube comments, and just give us a report, you know, what's going on? Do you recall changing this or did it happen automatically? And once you change it back to a comma, does it change back to a pipe separator in a day, a week, a month?
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