Separate numbers in a cell

[elsmith9035768]

Cell C3 has an amount (2.1). I need to break that number into two cells. The number before the period and the number after the period. C4 will be the number before the period (2) and C5 will be the number after the period (1). See example. Thanks.

 

[xlstime]

@elsmith9035768,

For first value
=INT(C3)
for second value
=VALUE(SUBSTITUTE((INT(C3)-D3),".",""))

Cell C3 has an amount (2.1). I need to break that number into two cells. The number before the period and the number after the period. C4 will be the number before the period (2) and C5 will be the number after the period (1). See example. Thanks.

 

[elsmith9035768]

The second formula returns a "2". When I changed "D3" to "D2" the result is "-1". I cannot figure out how to remove the negative sign before the "1". Thanks.

 

[bosco_yip]

A bit modified xlstime's 2nd formula :

=0+SUBSTITUTE((C3-INT(C3)),".","")

Or, another possible :

=0+MID(C3,FIND(".",C3)+1,16)

Regards
Bosco

 

[PCosta87]

Hi @elsmith9035768

Have you tried using text to columns in Data tab... there you can select what you want to be the delimiter, in this case the comma ","



Regards

 

[elsmith9035768]

Bosco,
The first formula works. But on 3 of the cells the result is (example: 1E+15). I assumed it was a cell formatting issues. I have General marked for the category. I appears the cell category is formatted for text, but it is not. I have tried all the categories but none work. Any ideas?

PCosat87, the cells to be separated are imported from another sheet and the text to columns function doesn't seem to be working.

 

[bosco_yip]

Bosco,
The first formula works. But on 3 of the cells the result is (example: 1E+15). I assumed it was a cell formatting issues. I have General marked for the category. I appears the cell category is formatted for text, but it is not. I have tried all the categories but none work. Any ideas?

1E+15 is a number format in scientific notation also known as exponential notation. It means 1 x10^15 (One times ten to the power of 15).

Since it is a whole number without decimal, the 2nd formula to workaround by wrapping a IFERROR function as this :

=IFERROR(1/(1/SUBSTITUTE((C4-INT(C4)),".","")),"")

Regards
Bosco