Split a number into digits and get the digit at any place value

Left-to-right text indexing vs right-to-left place value math

Account codes and routing numbers index digits from the LEFT (1st, 2nd, 3rd digit): VALUE(MID(A2, Pos, 1)) extracts the exact digit as a real number.

Arithmetic place values (ones = 10^0, tens = 10^1, hundreds = 10^2) index from the RIGHT: MOD(QUOTIENT(ABS(TRUNC(A2)), 10^Power), 10) isolates the exact place-value digit even if a shorter number has an implicit leading 0 (e.g., thousands digit of 482 = 0).

Place-value digit and Nth-from-left digit formulas

For 7,482 at Power = 2 (hundreds place), QUOTIENT(7482, 100) = 74 and MOD(74, 10) = 4.

=MOD(QUOTIENT(ABS(TRUNC(A2)), 10^B2), 10)

Nth digit from the left

Wrap VALUE(MID(...)) in IFERROR(..., 0) in case Pos exceeds the number of digits.

=IFERROR(VALUE(MID(ABS(TRUNC(A2)), C2, 1)), 0)

Inputs and interpretation

The arithmetic version removes the sign and truncates fractional digits. Use a nonnegative integer place power and a positive integer left position. A zero fallback for an absent left digit is a display policy, not evidence of a real zero digit. Store routing/account IDs as text to preserve leading zeros and values beyond spreadsheet numeric precision; this numeric example is not suitable for those identifiers unchanged.

Further reading

• Microsoft Support — QUOTIENT function