Lose to left of decimal

bam12

Well-known Member
Joined
Dec 6, 2004
Messages
985
How do I lose anything to left of decimal and show figure as whole number

0811294.12345. Would be 12345
 
811294.12344 12344
811294.12346 12346
811294.12348 12348
811294.12350 1235
811294.12352 12352
811294.12354 12354
811294.12356 12356
811294.12358 12358
2711294.12457 12457

here is sample of what i am getting
This works for me...

Book1
AB
2811294.1234412344
3811294.1234612346
4811294.1234812348
5811294.1235012350
6811294.1235212352
7811294.1235412354
8811294.1235612356
9811294.1235812358
102711294.1245712457
Sheet1

This formula entered in B2 and copied down:

=ROUND((A2-INT(A2))*100000,0)
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Suppose your data is in cell C5. Can you try this formula and post back?
=1*(RIGHT(C5,LEN(C5)-FIND(".",C5))&REPT(0,5-LEN(RIGHT(C5,LEN(C5)-FIND(".",C5)))))
(looks like Biff has a simpler solution, although mine appears to function nonetheless...)
Larry
 
Last edited:
Upvote 0
This formula also seems to work, although not as elegant as Biff's formula:
=REPLACE("00000",1,LEN(MID(A2,FIND(".",A2,1)+1,5)),MID(A2,FIND(".",A2,1)+1,5))
 
Upvote 0
Assuming you want a real number, what about this formula?

=--RIGHT(100000*A2,5)

If you need to preserve leading zeroes (the value will be text for this case), then use this formula instead...

=RIGHT(100000*A2,5)
 
Last edited:
Upvote 0
If you convert your original data to text it will work - see my previous post - so long as they remain as numbers it will treat 12345.67890 as 12345.6789 and appear to drop the final zero no matter what you do
 
Upvote 0
...so long as they remain as numbers it will treat 12345.67890 as 12345.6789 and appear to drop the final zero no matter what you do
Did you see the formulas I posted in Message #14? If you try them, you will see that what you said is not the case for my formulas.
 
Upvote 0

Forum statistics

Threads
1,225,130
Messages
6,183,026
Members
453,146
Latest member
scarabeovini

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
Back
Top