ignore decimal

[ck]

Hi,
I saw some posts regarding the ignoring of decimal points and the solutions.
What happens if a particular cell requires manual input and the decimal
places varies?

E.g.
1.25
89.1204
105.22

Require:
125
891204
10522

Any solutions for this? Thanks

 

[Dave Peterson]

I think I'd treat the entries as text and use a formula like:

=--substitute(a1,".","")

=substitute() returns a string.
the first minus converts the string to a number (but opposite sign). The second
minus changes the number back to the original sign.

 

[ck]

Hi Dave,

It works but i encounter a situation which I did not anticipate (error on my
part). If i key in 1.2521, it will reflect 12521 (ok) but if the figure is
suppose to be 1.200, then it shows 12 when it should be 1200.

What can be done to your formula to solve this issue? Or am i asking for too
much out of the capability of formulas?

entry: require Your solution
1.2521 12521 ok
1.2000 12000 it shows 12 instead of the required
88.49 8849 ok
88.50 8850 it shows 885 instead of the required

Thanks.

 

[David Biddulph]

In which case you'd have to enter as text, not as a number. When Excel
stores a number, it doesn't distinguish between 1.2 and 1.200.

 

[ck]

Tks to all for the help.

In which case you'd have to enter as text, not as a number. When Excel
stores a number, it doesn't distinguish between 1.2 and 1.200.
--
David Biddulph




.