Omitting the whole number when formatting decimals

[BunnyHop]

Hi,
I am trying to omit the whole number when it is copied into a new cell.

Ex. 29.8765 = .8765

I do not want a leading 0 either or for the decimal to be rounded up or
down, but copy exactly the last four digits behind and including the decimal
point.

I also would like to nest this formatting in a formula that combines copying
the number from the old cell to the new and then automatically format.

Any help would be appreciated.

Thanks,

 

[Joel]

use the MOD function with the mod parameter equal 1

=MOD(A1,1)

 

[Rick Rothstein]

How many digits do you want to show if there are only 2 decimal places after
the decimal point? What about if there are 7? Your "copy the last four
digits" comment is what raised this question.

 

[BunnyHop]

Hi,

Thanks. That worked but I still get a 0. Know of anyway to avoid that also?

 

[BunnyHop]

Hi,

It will always be 4 digits after the decimal. So, the formatting won't
include any more or less.

Thanks

 

[BunnyHop]

Hey Joel,

That worked great. Thank You.
I am going to be greedy with another question.
Is there any way to copy a range within a cell - Ex. 29.8765 - Only copy say
the whole number or just the decimal portion?

Thanks

 

[Rick Rothstein]

Use the =MOD(A1,1) formula Joel posted and custom format the cell like
this...

1. Select the cell the above formula is in
2. Click Format/Cells from the menu bar
3. Click the Number tab on the dialog box that appears
4. Select Custom from the list on the left
5 Enter .0000 (that is a dot followed by 4 zeroes) in the Type field
6. Click OK

The value in the selected cell should be displayed as you want.

 

[BunnyHop]

Thanks Rick,

Everything worked as great. Wondering now if it is possible to copy specific
data from within a range?

Ex. 29,8765 Only copy the whole number or only copy the decimal portion.

Thanks

 

[Joel]

After using MOD use a custom format

..####

Hi,

It will always be 4 digits after the decimal. So, the formatting won't
include any more or less.

Thanks

 

[Joel]

The best way to copy is to use the formula like I used. Then convert the
formula back to a number by copying and pastespecial using the value option
in pastespecial.

 

[alexrs2k]

Number Result Applied Formula
4565.2453563 .2453563 =VALUE(RIGHT(B14,LEN(B14)-FIND(".",B14)+1))
4565.2453563 .2454 =MOD(B14,1)
4565.2453563 4565 =VALUE(LEFT(B16,FIND(".",B16)))

 

[alexrs2k]

Also you could try TRUNC function.

Thanks Rick,

Everything worked as great. Wondering now if it is possible to copy specific
data from within a range?

Ex. 29,8765 Only copy the whole number or only copy the decimal portion.

Thanks