Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,

Omitting the whole number when formatting decimals
 

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,