Google Sheets how to extract nth decimal number using INT() or TRUNC()?

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 :

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 ?

• 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. Jan 6 at 17:35
• @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... 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` Jan 8 at 21:36
• The second link therein is broken. Jan 8 at 22:20

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))```

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.

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?

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