The other thing you'll see is in the tool tip, those commas will be replaced by pipes.
So =VLOOKUP of this, comma- I guess it's already working, so that's awesome- 2, False.Ĭomma stopped working in formulas because, somehow, the list separator got changed.Ĭheck out my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.Ĭlick that "I" on the top right hand corner.Īlright, 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.Ĭlever- 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. See already the commas are back to regular commas, and let's make sure that we can still enter things.
#Do you need commas in excel formulas windows 10#
Now if you're on Windows 10: If you're in Windows 10, its 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. 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. So I just search up here for Regional Language, Change the date, time or number format.
#Do you need commas in excel formulas windows 7#
Now, I'm in Windows 7 on this machine, and it's a different Settings in Windows 10- I'll show you those. 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.Īnd then you can define the delimiter for one file only, instead of just globally changing the list separator.Īlright, so here's what we have to do: We have to go to Start, and then Control Panel. So whoever wrote that website to change the list separator from a comma to a pipe must never enter formulas in their life, which is crazy.Īlright, 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. Oh, geez, you never want to change anything in the Control Panel because it has wide repercussions throughout all of your programs, and polluting the list separators used in our formulas.
Who's telling people to change the list separator?Īnd 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. 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.Īll right, so I talked to the Excel team and they're saying hey, yeah, we're seeing this happen increasingly. His keyboard doesn't have a pipe, alright? So there's a backslash, and then if you Shift that there's a pipe character.īut Scott, he was using some sort of a virtual system. 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. If you're new to formulas- but- you know, you don't have to do this every single time.Ĭolumn 2, False- and he pressed okay, and then he looks at the formula, and instead of commas, he has what he calls a weird character. 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. Why do they think I've finished the formula?Īnd Scott- I have to give Scott credit- Scott was brilliant here. Oh, yeah, there's a problem with this one. =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. So I'm going to create a little VLOOKUP here, right? 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. Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Learn Excel for MrExcel Podcast, Episode 2222: Commas Stop Working in Formulas.
0 kommentar(er)
