Exceljet

Quick, clean, and to the point

Get number at place value

Excel formula: Get number at place value
Generic formula 
=MOD(number,place*10) - MOD(number,place)
Explanation 

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.

How this formula works

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
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables