I need to extract the numbers after the 4 decimal places - so I need in the example 99999999
Um, okay. But your title says "
minus decimal places
from...", and your original description is ``I would like to extract or
minus all the digits
after...``. The word "minus" can be interpreted as "subtract from".
So, perhaps you can understand why
@Fluff suggested ROUNDDOWN, which effectively subtracts 0.00009999999 from 5133.15969999999 (7 nines, not 8, BTW).
the round option only gives me the whole number and the 4 dp. How do I extract the numbers after that?
It is unclear whether you want 9999999 as a number or as text, or even 0.00009999999.
I would recommend a text result. Otherwise, we cannot distinguish the "excess" fractional digits in 5133.159699 from 5133.15960000009, for example.
To that end, you might use the following (A1 is the original value, 5133.15969999999):
=MID(TEXT(A1, "0."&REPT("#",127)), 5+FIND(".", TEXT(A1, "0."&REPT("#",127))), 127)
(Apparently, 127 is the max number of decimal places that Excel allows in that form.)
However, the range and form of the original values are unclear. It might be helpful to have more examples, as well as an explanation of: (a) the circumstances that result in 5133.15969999999; (b) why you want to extract the "excess" fractional digits; and (c) what you would want when there are (arguably) fewer "legitimate" fractional digits.
(For example, would you consider 51331.5969999999 to be 51331.596 with "excess" fractional digits 0.0009999999; or 51331.5969 with "excess" 0.0000999999? What if I told you that the origin is the same, simply scaled up by 10?
In fact, I wonder if there is a "typo" in your original posting, and you intended the example to be 5133.
95709999999 (!).
I know: that's one heckuva of typo. But assuming that the
intended value of the original example (as written) is 5133.1597, it seems like an unlikely coincidence that a rearrangement of those digits -- 5133.9571 -- exposes the relatively unusual Excel formatting defect that displays it as 5133.95709999999 and that actually changes it to the binary approximation of 5133.95709999999 in some circumstances. Perhaps you simply posted the wrong example.
In any case, it might be more productive (for your benefit) to discuss how to deal with whatever the circumstances are, instead of how to separate the "excess" fractional digits, one way or another.
