2

I attempted to extract the first decimal number from a dataset through a very simplistic use of INT(), and was dumbfounded to discover that both INT() and TRUNC() can return 0 out of a calculated 1 :

enter image description here

First of all, would it be possible to explain this behaviour ? Did I miss something ?

Then, what would be a good way to actually extract the nth decimal number from each number of my dataset while avoiding this pitfall ?

4
  • 1
    I confirmed the behavior you described but can't yet explain it. I provided an approach to get your digit. The problem doesn't occur if there are digits in the 2nd position to the right of the decimal!? Interesting.
    – Blindspots
    Jan 6 at 17:35
  • 1
    @Blindspots Thanks for the detailed answer with explanations and edge/type cases ! Encapsulated it into value() to get back integers, seems to work like a charm. Relieved that you could replicate my problem and found yourself equally surprised ; and obviously still extremely curious as to why this happens...
    – klonaway
    Jan 7 at 10:39
  • FYI I updated my answer to use VALUE for MOD's dividend instead of *1 because it would fail specifically for currency as pointed out by doubleunary. My testing methodology was flawed as I tested currency using accounting which looks similar but behaves differently. The current approach should be =MID(MOD(VALUE(A1), 1), 3, 1)*1
    – Blindspots
    Jan 8 at 21:36
  • The second link therein is broken. Jan 8 at 22:20

2 Answers 2

1

Formula

Returns digit in specified position of the non-integer portion of a number.

=MID(MOD(VALUE(A1), 1), 3, 1)   //  returns number-as-text

=MID(MOD(VALUE(A1),1), 3, 1)*1  //  returns number

Explanation

  1. MID's string argument is a MOD function, so,

    =MID(string, starting_at, extract_length)
    

    becomes

    =MID(MOD(dividend, divisor),
       starting_at, extract_length)
    
    • MOD returns the non-integer portion (remainder) of a dividend, and a divisor which in the case of 6.1 and 1 is 0.1
    • The dividend is placed inside a VALUE function so that regardless of its number format, MID returns results based on the underlying number.
  2. MID's starting_at is set to 3, the character to the right of the decimal in 0.1

  3. MID's extract_length is set to 1 character

    =MID(MOD(VALUE(A1), 1), 3, 1)
    =MID(MOD(6.1, 1), 3, 1)
    =MID(0.1, 3, 1)
    =1
Notes:
  • If A1 is text, a #VALUE! error will be returned, the formula can be wrapped in IFERROR to handle it if this is a concern.
  • If A1 is an empty cell, or there is no digit in the position specified, an empty value will be returned.
  • Result is a string. Sheets will coerce it back to an integer if used in a calculation. For example,
    =ISNUMBER("5")=FALSE
    # but,
    =ISNUMBER("5"*1)=TRUE
  • this can done preemptively by multiplying the formula by one or placing it inside the VALUE function. For example,
    =MID(MOD(VALUE(A1), 1), 3, 1)*1
    # or
    =VALUE(MID(MOD(VALUE(A1), 1), 3, 1))
0
1

To get the first digit after decimal mark, use regexextract(), like this:

=regexextract(to_text(A1), "\.(\d)")

To get the second digit, use "\.\d(\d)", to get the third, use "\.\d\d(\d)", and so on.

See regular expressions, regexextract() and RE2.

dumbfounded to discover that both INT() and TRUNC() can return 0 out of a calculated 1

You are asking why a spreadsheet would consider the values 6.1 and 6.11 differently in this context:

int(10 * (6.1 - int(6.1))) === 0

int(10 * (6.11 - int(6.11))) === 1

This behavior is caused by the rounding rules mandated by the IEEE 754 standard. See Why are floating point numbers inaccurate?

1
  • Thanks for the link!
    – Blindspots
    Jan 8 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.