Retrieving units and tens in Excel

Thread Starter

atferrari

Joined Jan 6, 2004
4,764
Given a result of a previous calculation, (always positive and > 1000) I want Excel to show in another cell just the units and tens of that result.

Let's say I have 22768.

I need Excel to show just 68 (not as text but as a numeric value).

The value 22700 is unknow beforehand thus no substraction possible.

Any idea how to obtain that?
 

kubeek

Joined Sep 20, 2005
5,794
in programming language it would be n%100, which means the rest after division by 100. Don't know how it is in excel.
 

Papabravo

Joined Feb 24, 2006
21,159
The % notation in C is for the remainder after integer division. The MOD worksheet function is what you want:

Syntax
MOD(number,divisor)
Number is the number for which you want to find the remainder.
Divisor is the number by which you want to divide number.

Example: Cell A1 contains 22768
Cell B1 contains =MOD(A1,100) and the result is 68
 

zgozvrm

Joined Oct 24, 2009
115
Given a result of a previous calculation, (always positive and > 1000) I want Excel to show in another cell just the units and tens of that result.

Let's say I have 22768.

I need Excel to show just 68 (not as text but as a numeric value).

The value 22700 is unknow beforehand thus no substraction possible.

Any idea how to obtain that?
Let's assume your value, 22768 resides in cell A1.

First, use the formula "=RIGHT(A1,2)" (enter this formula in cell A2.
This gives us "68" as text.

Then, use the formula "=VALUE(A2)" in cell A3 which converts the text value "68" to the numerical value 68.

Of course, you can combine these into one formula (let's put it in cell B1):
"=VALUE(RIGHT(A1,2))"
 

Papabravo

Joined Feb 24, 2006
21,159
Let's assume your value, 22768 resides in cell A1.

First, use the formula "=RIGHT(A1,2)" (enter this formula in cell A2.
This gives us "68" as text.

Then, use the formula "=VALUE(A2)" in cell A3 which converts the text value "68" to the numerical value 68.

Of course, you can combine these into one formula (let's put it in cell B1):
"=VALUE(RIGHT(A1,2))"
Your method works as long as the cell contains an integer. The original problem was to isolate the tens and the units of a numeric quantity.
 

zgozvrm

Joined Oct 24, 2009
115
Your method works as long as the cell contains an integer. The original problem was to isolate the tens and the units of a numeric quantity.
An integer IS a numeric quantity!
I think you meant to say that my method works as long as the cell contains a string.

My method actually works for either (try it)! Although RIGHT is a string function, it works just as well with numerical values, but the result will be a string. That is why I surrounded it by the VALUE function.

This is just an alternative to the MOD function method you gave.

For floating point numbers (eg. 38726.48297), if you wanted to isolate the 1's and 10's (26), you could use:
=INT(MOD(A1,100))
 

Papabravo

Joined Feb 24, 2006
21,159
Yes of course you are correct about the RIGHT function. What I meant was the the numeric quantity 22768.75 gives different answers with the two methods. Not necessarily the intended answer in either case.

I think B1=INT(MOD(A1,100)) might fix that problem
 

Papabravo

Joined Feb 24, 2006
21,159
And I agreed with you, no need to be defensive, but I did not actually go and test it. Age and experience do accord one some perks.
 

zgozvrm

Joined Oct 24, 2009
115
And I agreed with you, no need to be defensive, but I did not actually go and test it. Age and experience do accord one some perks.
Sorry, I wasn't being defensive... it just seemed as if you were stating something that I had already stated and weren't really sure if it worked.

I was simply stating that I had already said that, and that I had verified that it works.

No malice intended!
 
Top