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,

use the MOD function with the mod parameter equal 1

=MOD(A1,1)



"BunnyHop" wrote:

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,

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

Hi,

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


"Joel" wrote:

use the MOD function with the mod parameter equal 1

=MOD(A1,1)



"BunnyHop" wrote:

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,

Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

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


"Joel" wrote:

After using MOD use a custom format

.####



"BunnyHop" wrote:

Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

After using MOD use a custom format

..####



"BunnyHop" wrote:

Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

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.

"BunnyHop" wrote:

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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

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)))


"BunnyHop" wrote:

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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,

Also you could try TRUNC function.

"BunnyHop" wrote:

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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
Hi,

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

Thanks

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"BunnyHop" wrote in message
...
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,