Commas Stop Working in Formulas

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?