Insert Two Decimal Points In Excel - 2382 (with corrections)

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 15, 2021 .
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.
maxresdefault.jpg


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.
 
  • Like
Reactions: alz

Watch MrExcel Video

Forum statistics

Threads
1,122,937
Messages
5,598,956
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top