25kasa1 has numbers with the decimal points missing. How can you quickly change 5000 to 50.00 in an entire column. In less than 2 minutes and 40 seconds, this video shows 6 methods PLUS a way to prevent the problem in the first place. Includes Formulas, Power Query, VBA, and Paste Special.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2364.
How to insert missing decimal points in Excel.
Welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question from 25Kasa1.
How do I insert a decimal between numbers. Change 5000 to 50.00?
There are six easy ways to do this.
There is probably 100 ways to do it..
The first one. =A4*.01. Double click to copy that down.
Grab the right edge with the right mouse button. Drag left. Let Go.
Copy Here As Values Only.
Here. =D4/100. Same thing. Double-click to copy it down.
Right-drag. Copy Here As Values Only.
This is my favorite. Type .01 in a cell. Ctrl+C to Copy.
Select the whole range.
Paste Special. That is Alt+E S.
Then choose Multiply.
It will multiply everything by 1/100th.
Or put 100 in a cell. Copy that. Paste Special. Choose Divide. That is an "i" for divide.
Everything gets divided by 100.
Or, how about this? A little VBA.
Three lines of code. It could be two lines of code.
For each cell in Selection.
Cell.value = Cell.Value divide by 100.
Next.
Select that whole range. I've assigned the macro to Ctrl+D.
Bam! We are done.
Or, the one that will take the longest.
Data, From Table or Range. This is Power Query.
Select the whole range. Under Transform, Standard, Divide by 100.
Click OK.
Home, Close and Load To.
Existing worksheet. This cell right there. .
Click OK.
And there are the answers.
But, hey. if someone keyed this data and they did not put the decimal places in.
Let's just prevent the problem in the first place.
Check out this awesome trick.
File, Options, Advanced.
Automatically Insert a Decimal Point.
I will set that up to 2 decimal places.
Click OK When they type 720, Excel puts in 7.20.
They type 1483, it automatically puts in the decimal place.
Type 123456. You will get 1234.56.
What an awesome awesome trick.
If you like these tips, please Subscribe, Like, and Ring the Bell.
Thanks to 25Kasa1 for sending that question in.
Thanks to you for stopping by. We will see you next time for another netcast from MrExcel.
How to insert missing decimal points in Excel.
Welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question from 25Kasa1.
How do I insert a decimal between numbers. Change 5000 to 50.00?
There are six easy ways to do this.
There is probably 100 ways to do it..
The first one. =A4*.01. Double click to copy that down.
Grab the right edge with the right mouse button. Drag left. Let Go.
Copy Here As Values Only.
Here. =D4/100. Same thing. Double-click to copy it down.
Right-drag. Copy Here As Values Only.
This is my favorite. Type .01 in a cell. Ctrl+C to Copy.
Select the whole range.
Paste Special. That is Alt+E S.
Then choose Multiply.
It will multiply everything by 1/100th.
Or put 100 in a cell. Copy that. Paste Special. Choose Divide. That is an "i" for divide.
Everything gets divided by 100.
Or, how about this? A little VBA.
Three lines of code. It could be two lines of code.
For each cell in Selection.
Cell.value = Cell.Value divide by 100.
Next.
Select that whole range. I've assigned the macro to Ctrl+D.
Bam! We are done.
Or, the one that will take the longest.
Data, From Table or Range. This is Power Query.
Select the whole range. Under Transform, Standard, Divide by 100.
Click OK.
Home, Close and Load To.
Existing worksheet. This cell right there. .
Click OK.
And there are the answers.
But, hey. if someone keyed this data and they did not put the decimal places in.
Let's just prevent the problem in the first place.
Check out this awesome trick.
File, Options, Advanced.
Automatically Insert a Decimal Point.
I will set that up to 2 decimal places.
Click OK When they type 720, Excel puts in 7.20.
They type 1483, it automatically puts in the decimal place.
Type 123456. You will get 1234.56.
What an awesome awesome trick.
If you like these tips, please Subscribe, Like, and Ring the Bell.
Thanks to 25Kasa1 for sending that question in.
Thanks to you for stopping by. We will see you next time for another netcast from MrExcel.
