spliting whole number and decimal

[Don Doan]

Hi there,
I have some questions kinda cirle the same issue...so just bear with me here:

If my cell A1 has number 1234.56, and I want cell B1 contains 1234 and cell
B2 contains .56, How do I do that?

How do I do the same thing if there are 3 decimal places.. i.e 123.456

What happen if the format in cell A1 is a text? Can the splitting be done?

Thanks.

 

[Mike H]

Hi,

In B1
=INT(A1)

and in b2
=A1-INT(A1)

Mike

 

[FSt1]

another way....
in b1, enter =A1-MOD(A1,1)
in b2. enter =MOD(A1,1)

regards
FSt1

 

[Joe User]

How do I do the same thing if there are 3 decimal places.. i.e 123.456
What happen if the format in cell A1 is a text? Can the splitting be done?

The following should work in all cases:

B1: =INT(--A1)

B2: =A1 - B1

The formula in B1 does assume that if A1 is text, it is recognizable as a
number. If B1 results in a #VALUE! error, that assumption is wrong. In
that case, we may need to see exactly what text is in A1.


----- original message -----

 

[Joe User]

PS....

The following should work in all cases:
B1: =INT(--A1)

Apparenty, "--" is unneeded with INT. INT(A1) should work for text as well
as numbers.

Sigh, I wish Excel were not so inconsistent, although it works in your favor
this time.


----- original message -----

 

[ADB_Seeker]

Another way to do this is to use the Text to Columns Wizard. Place your
cursor in the column containing the content to be separated, then select Text
to Columns (in Excel 2007 it is in the Data/Data Tools ribbon; in Excel 2003
it is under Tools).

Following are the responses to the prompts:
—Original data type = delimited (click on Next)
—Delimeters = Check other (make sure all other options are unchecked). Enter
a period in the box (click on Next)
—Column Data Format = General
—Destination = the two cells in which the final results will be placed. The
cell in which your original data resides is shown. Highlight it and then
click in the two cells in your spreadsheet in which you want the final
results to be placed. (Click on Finish)

This works no matter how many numbers you have after the decimal (period) or
whatever you choose for your separator.