Splitting Cell Value By whole number

[blimish]

Hello, I hope someone can help me out here. I would like to do is take the value of a cell and split it into thousands and hundreds into another cell. For instance.

Source Cell = 12500
Need to get all the thousands into a cell
Thousands Cell = 12
Thousands cell gets x by 2.30
Thousands Cell = 12*2.30
I used the left function to extract data. But however I'm limited to only what is to the left 2. The thousands place may increase to left 3 or even 4. This causes my first problem. I want to be able to determine the positioning the multiply by 2.30. Hence, if 12500 was 112500 I would need to separate the 112 from the number and x by 2.30
Now for the hundreds.
Source Cell = 12500
Need to get all the hundreds into a separate cell
Hundreds Cell = 500
I used the Right function to separate the number by 3 places. After separating I need just the 5. The 5 would get multiplied by .20. Even if the number was 576 I would still only need the 5. So I tried to combine a formula Right (D#, 3), the go Left by 1 to pick up the five but it will not work. I hope somebody understands this..

Please Help,

 

[TonyJollans]

Hi blimish,

You want to use integer division and remainder functions to break down numbers kike this.

Thousands = Int(Number / 1000) in your example 12
TotalHundreds = Int(Number / 100) in your example 125

HundredsTensAndUnits = Number Mod 1000 in your examples 500 and 576
Hundreds = Int(HundredsTensAndUnits / 100) in your examples 5

combining the above gives
Hundreds = Int((Number Mod 1000) / 100) in your examples 5

and so on, and so on ...

Enjoy,
Tony

 

[SkipVought]

Hi,

First you can get the thousands by taking the integer part of the quotient...

=INT(Source/1000)

Then the hunderds part can be derived by...

=INT((Source-Thousands*1000)/100)

Take those value and manipulate them any way you like

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[blimish]

Thanks,

I got the 1000 extracting but however I'm still have problems with the 100 numbers

Data
B3 = 12500
C3 = =Int(B3/1000)*2.30 = 27.6
D3 = I need just the 5 out of the B3 (12500) cell and multiply it by .20


Thanks,

 

[TonyJollans]

Hi blimish,

Sorry, my examples of Mod were in VBA format (this is the VBA Forum) - the format to use in a cell is =MOD(Value,Modulus).

Just in time seen your new post - use

D3: =INT(MOD(B3,1000)/100)*0.2

or, a la Skip ..

D3: =INT((B3-INT(B3/1000))/100)*0.2

Enjoy,
Tony

 

[blimish]

Thank you,

Works Great!