Well, we are going off the beaten path today, for something incredibly arcane. Several people in a company have a problem where the comma format in Excel is showing only one comma, even when you have 1234,567.89. This problem might be unique to the tiny dual-island nation of Trinidad and Tobago. Episode 1893 shows you why.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Podcast Episode 1893: Why Only One Comma in 1234,567.89?
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Yeah, I've been gone for a while.
I was doing some seminars down in the country of Trinidad.
Oh my gosh, what a beautiful place this is.
Check this out.
We hiked about 1.2 kilometers up through the woods.
Look how sweaty I was.
It was a hot, hot day but it was well worth it.
This was the most beautiful spot I've ever seen on the planet.
You can't believe how green that water was and that waterfall.
Some young people who were behind us, they climbed right up that rope and they jumped into the waterfall but not me.
Anyway, here's our question and this one will drive you crazy.
See I have a number up there, 1234567.89?
I'm in Excel and I want to come up here and apply the comma format COMMA STYLE, right?
Oh, what's up with that?
I get the .89 and I get the , between the thousands and the hundreds but I'm not getting a , there between the 1,000,000 and the 234,000.
Wow.
That is really bizarre.
So, someone asked this question in my seminar and I'm like, oh wow, and I went into FILE, OPTIONS, you know, looked for things there, and it turns out, it turns out, that this is under REGION AND LANGUAGE.
Alright.
So, I'm going to choose REGION AND LANGUAGE.
I'm going to zoom in here, and I actually got my laptop and their laptop and we started studying every one of these items, and they all matched.
Then I came down here and went to ADDITIONAL SETTINGS and they all matched.
See, the DIGIT GROUPING SYMBOL is a COMMA, right, and everything looks exactly the same from their system to my system, and we were like what the heck is up with this.
Alright, so, if you happen to be caught with this problem -- why are we not getting the second comma between the millions and the hundred thousands -- it turns out you come here to REGION AND LANGUAGE and none of these settings that, you know, are obviously screaming down here at you are the actual option.
You have to come to this drop-down where it's currently ENGLISH (TRINIDAD AND TOBAGO) and switch to ENGLISH (UNITED STATES).
Alright.
So, now, I'm in Trinidad and I say to them, I say, well, what's up with that?
Is there some rule here that you don't use that second comma?
And they all looked at me and said, no, we don't have that rule at all.
So, whoever told Microsoft that that regional setting needs to be missing that comma, yeah, I don't know who it was or why but, clearly, you don't need it.
Now, there is a really, really obscure trick, but, you know, you never know.
[ I always…when I do the podcast, you say, well, is this one going to get a lot of traffic or not a lot of traffic?
I bet people that are having this particular problem, you know, it is a strange one – 02:51 ].
So, to all my friends in Trinidad, if you are having this problem, that's how to solve it, and, wow, what a beautiful, beautiful island you have.
Alright, hey, I want a thank you for stopping by.
We’ll see you next time for other netcast from MrExcel.
Learn Excel from MrExcel, Podcast Episode 1893: Why Only One Comma in 1234,567.89?
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Yeah, I've been gone for a while.
I was doing some seminars down in the country of Trinidad.
Oh my gosh, what a beautiful place this is.
Check this out.
We hiked about 1.2 kilometers up through the woods.
Look how sweaty I was.
It was a hot, hot day but it was well worth it.
This was the most beautiful spot I've ever seen on the planet.
You can't believe how green that water was and that waterfall.
Some young people who were behind us, they climbed right up that rope and they jumped into the waterfall but not me.
Anyway, here's our question and this one will drive you crazy.
See I have a number up there, 1234567.89?
I'm in Excel and I want to come up here and apply the comma format COMMA STYLE, right?
Oh, what's up with that?
I get the .89 and I get the , between the thousands and the hundreds but I'm not getting a , there between the 1,000,000 and the 234,000.
Wow.
That is really bizarre.
So, someone asked this question in my seminar and I'm like, oh wow, and I went into FILE, OPTIONS, you know, looked for things there, and it turns out, it turns out, that this is under REGION AND LANGUAGE.
Alright.
So, I'm going to choose REGION AND LANGUAGE.
I'm going to zoom in here, and I actually got my laptop and their laptop and we started studying every one of these items, and they all matched.
Then I came down here and went to ADDITIONAL SETTINGS and they all matched.
See, the DIGIT GROUPING SYMBOL is a COMMA, right, and everything looks exactly the same from their system to my system, and we were like what the heck is up with this.
Alright, so, if you happen to be caught with this problem -- why are we not getting the second comma between the millions and the hundred thousands -- it turns out you come here to REGION AND LANGUAGE and none of these settings that, you know, are obviously screaming down here at you are the actual option.
You have to come to this drop-down where it's currently ENGLISH (TRINIDAD AND TOBAGO) and switch to ENGLISH (UNITED STATES).
Alright.
So, now, I'm in Trinidad and I say to them, I say, well, what's up with that?
Is there some rule here that you don't use that second comma?
And they all looked at me and said, no, we don't have that rule at all.
So, whoever told Microsoft that that regional setting needs to be missing that comma, yeah, I don't know who it was or why but, clearly, you don't need it.
Now, there is a really, really obscure trick, but, you know, you never know.
[ I always…when I do the podcast, you say, well, is this one going to get a lot of traffic or not a lot of traffic?
I bet people that are having this particular problem, you know, it is a strange one – 02:51 ].
So, to all my friends in Trinidad, if you are having this problem, that's how to solve it, and, wow, what a beautiful, beautiful island you have.
Alright, hey, I want a thank you for stopping by.
We’ll see you next time for other netcast from MrExcel.
