Summary

To get the number at a specific place value you can use a formula based on the MOD function. By place value, we mean hundred thousands, ten thousands, thousands, hundreds, tens, ones, etc.

In the example shown, the formula in cell D8 is:

=MOD(B8,C8*10) - MOD(B8,C8)

The place is thousands (1000), and the result is 3000.

Generic formula

=MOD(number,place*10) - MOD(number,place)

Explanation 

The MOD function performs the modulo operation. It takes a number and a divisor and returns the remainder after division.

In this formula, we subtract one MOD result from another.

For the first MOD, we use the number with a divisor that equals the place value we want times 10. For the second MOD, we use the number with a divisor equals to the place value we seek. The formula is solved like this:

=MOD(B8,C8*10)-MOD(B8,C8)
=MOD(123456,1000*10)-MOD(123456,1000)
=3456-456
=3000

Related functions

Excel MOD function

MOD Function

The Excel MOD function returns the remainder of two numbers after division.  For example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Functions