Extracting characters from left and right of a decimal point

[ronnie_m]

Hi Folks
In Excel 2000, I need to select the values on either side of a decimal point in a number formatted as either number (2 dec. places) or custom (###.##) to use subsequent calculations. The LEFT function works well enough when the number of places to the left is constant, but because the portion to the right can vary from 1 to ?(15 I think) places, subsequent calculations based on RIGHT are inaccurate. Any ideas??

 

[TheNoocH]

try this

=RIGHT(A1,LEN(A1)-FIND(".",A1))

and this

=LEFT(A1,FIND(".",A1)-1)

Note: this turns the data into TEXT....

you stated you were going to do calcs....try this
=VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1)))
and
=VALUE(LEFT(A1,FIND(".",A1)-1))

NOTE: you could also try TEXT to COLUMNS with the decimal point as your delimiter

 

[Scott Huish]

Instead of using string functions, try this:

To get the number to the left of the decimal point:

=INT(A1)

to get the numbers after the decimal point:
=A1-INT(A1)

 

[TheNoocH]

Instead of using string functions, try this:

To get the number to the left of the decimal point:

=INT(A1)

to get the numbers after the decimal point:
=A1-INT(A1)

Nice...always forget about INT....

 

[pmn]

for the decimal portion, you may also use the modulo function which returns the remainder (remember long division) from a division calculation.

for example:
=MOD(10,3) => 1

__3_ R1
3)10
9
----
1

This is normally only used with integers (whole numbers, no decimals), but the Excel function take floating point numbers (with decimals) as well, so
=MOD(10.1, 3) => 1.1

and to get what you are looking for, use this:

=MOD(A1,1)