Dinesh is trying to insert two decimal points in a number. Excel has some strange mojo going on with the TEXT function. This video takes a look at some formula methods and then perhaps the easiest way: Flash Fill.
A note: while working on the captions for this video, I realized the formulas starting at 0:44 were wrong (they were set up for 000-00-0000 instead of 00-000-000 like Dinesh wanted. I deleted the original 2382 an uploaded this new 2382. Thanks to those of you who commented on the old 2382 and sorry to lose those comments.
A note: while working on the captions for this video, I realized the formulas starting at 0:44 were wrong (they were set up for 000-00-0000 instead of 00-000-000 like Dinesh wanted. I deleted the original 2382 an uploaded this new 2382. Thanks to those of you who commented on the old 2382 and sorry to lose those comments.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2382.
Insert two decimal points.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question actually came in on YouTube from an old old video I had.
Where they were trying to insert two decimal places.
That's not what the guy was trying to do at all.
He was trying to insert two decimal points.
He had a part number and needed a period to appear two times.
Turns out, it is not easy to do.
Let's get started.
Oh, hey, first off: Sorry to Dinesh who ended up at 2207.
Which says “Insert two decimals”.
I was talking about shifting the decimal place over by two places.
He actually has a part number and needs to insert a period and a period.
This is one where it took a couple of days for me to hone in on this.
You know the first the first thought is the LEFT of this Comma two.
And then an ampersand period ampersand.
And then the MID starting at three for a length of three.
And then another period and then RIGHT.
But there has to be a better way.
So my first my first inclination is let's just throw it in there using TEXT.
But that does not work, right?
Why doesn't it work?
It should work. I mean, after all, the Social Security special format would certainly insert two hyphens in a number.
But for some weird reason - the periods – you are not allowed to have two periods in a number.
I mean: we are converting it to text.
Why do they care?
Alright so the the actual answer if you go to 2207 that I posted back to Dinesh is.
“I am sorry I have to do this, but let's put hyphens in and then use the SUBSTITUTE function to change the hyphens to periods”.
And, for a couple of days that was the answer.
But, that just didn't feel right.
There had to be something better than that.
Think about when we are adding a comma afterwards to put things in thousands.
You can just put a K there.
But, if you're putting two commas to show things in millions.
You can't put an M.
You have to put a backslash M Why? because M already means something to Excel.
It means months in dates or minutes in time.
So the backslash M must be the code to tell Excel, “hey, don't treat this next thing as any kind of a formatting character just blindly put it in the number format”..
So then finally 48 hours after the question came in the first formula that I was actually happy with.
=TEXT of B10 and the formatting code is three zeros, backslash period to put the first period in.
Two more zeros, backslash period to put the next period in.
And then three more zeros.
Now, I know, right down in the YouTube comments you're already typing “Bill, there's an easier way!” Boy, and there is an easier way: Flash Fill.
Ever since Excel 2013 would make this very very easy.
So we have the 94870459.
Type it the way you want it to appear 94 dot 870 dot 459.
Then come here to the blank cell and Control+E or Data.
Flash Fill and it will follow your lead and put those periods in.
Much much easier than the 48 hour method that I went through.
Well, hey, if you like these tips please Like, Subscribe, Ring that bell.
Feel free to post any questions or comments down in the comments below.
Thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Insert two decimal points.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question actually came in on YouTube from an old old video I had.
Where they were trying to insert two decimal places.
That's not what the guy was trying to do at all.
He was trying to insert two decimal points.
He had a part number and needed a period to appear two times.
Turns out, it is not easy to do.
Let's get started.
Oh, hey, first off: Sorry to Dinesh who ended up at 2207.
Which says “Insert two decimals”.
I was talking about shifting the decimal place over by two places.
He actually has a part number and needs to insert a period and a period.
This is one where it took a couple of days for me to hone in on this.
You know the first the first thought is the LEFT of this Comma two.
And then an ampersand period ampersand.
And then the MID starting at three for a length of three.
And then another period and then RIGHT.
But there has to be a better way.
So my first my first inclination is let's just throw it in there using TEXT.
But that does not work, right?
Why doesn't it work?
It should work. I mean, after all, the Social Security special format would certainly insert two hyphens in a number.
But for some weird reason - the periods – you are not allowed to have two periods in a number.
I mean: we are converting it to text.
Why do they care?
Alright so the the actual answer if you go to 2207 that I posted back to Dinesh is.
“I am sorry I have to do this, but let's put hyphens in and then use the SUBSTITUTE function to change the hyphens to periods”.
And, for a couple of days that was the answer.
But, that just didn't feel right.
There had to be something better than that.
Think about when we are adding a comma afterwards to put things in thousands.
You can just put a K there.
But, if you're putting two commas to show things in millions.
You can't put an M.
You have to put a backslash M Why? because M already means something to Excel.
It means months in dates or minutes in time.
So the backslash M must be the code to tell Excel, “hey, don't treat this next thing as any kind of a formatting character just blindly put it in the number format”..
So then finally 48 hours after the question came in the first formula that I was actually happy with.
=TEXT of B10 and the formatting code is three zeros, backslash period to put the first period in.
Two more zeros, backslash period to put the next period in.
And then three more zeros.
Now, I know, right down in the YouTube comments you're already typing “Bill, there's an easier way!” Boy, and there is an easier way: Flash Fill.
Ever since Excel 2013 would make this very very easy.
So we have the 94870459.
Type it the way you want it to appear 94 dot 870 dot 459.
Then come here to the blank cell and Control+E or Data.
Flash Fill and it will follow your lead and put those periods in.
Much much easier than the 48 hour method that I went through.
Well, hey, if you like these tips please Like, Subscribe, Ring that bell.
Feel free to post any questions or comments down in the comments below.
Thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
