Splitting A number into four differnet cells

**Everquest** · 04-21-2007, 06:26 PM

I need to split a number into 4 different cells, depending on what the number is.
If the number 165,735 were entered, I would need the four cells to show:
Cell 1 = 1
Cell 2 = 6
Cell 3 = 57
Cell 4 = 35
In short, it takes 100 in Cell 4 to make 1 in Cell 3. 100 in Cell 3 would make 1 in cell 2, etc...

I have no idea how to go about doing this. Thanks for any help you can give.

**Leith Ross** · 04-21-2007, 06:45 PM

Hello Everquest,

The logic escapes me. At first it looked like you were doing Modulus 100 math, but no. Can you further explain this odd separation's rules?

Sincerely,
Leith Ross

**davesexcel** · 04-21-2007, 09:01 PM

Originally Posted by Everquest

I need to split a number into 4 different cells, depending on what the number is.
If the number 165,735 were entered, I would need the four cells to show:
Cell 1 = 1
Cell 2 = 6
Cell 3 = 57
Cell 4 = 35
In short, it takes 100 in Cell 4 to make 1 in Cell 3. 100 in Cell 3 would make 1 in cell 2, etc...

I have no idea how to go about doing this. Thanks for any help you can give.

I suppose you could use text to columns, this is just a recroded macro

Please Login or Register  to view this content.

if the value is always 6 digits then you could use something like this

=LEFT(A1,1)
=MID(A1,2,1)
=MID(A1,3,2)
=RIGHT(A1,2)

**Wheres_the_Red** · 04-23-2007, 03:49 AM

To use formula, with 165735 keyed to A1:

Please Login or Register  to view this content.

Assumes your number is a whole number no higher than 999,999

**Everquest** · 04-24-2007, 03:01 PM

I just realized that I messed up my original post. The number 165,735 would fall into these cells:
Cell 1 = 0
Cell 2 = 16
Cell 3 = 57
Cell 4 = 35

The number 1,165,735 would fall into:
Cell 1 = 1
Cell 2 = 16
Cell 3 = 57
Cell 4 = 35

The number will go no higher than 999,999,999

100 of Cell 4 = 1 of Cell 3. 10,000 of Cell 4 = one of Cell 2. 1,000,000 of Cell 4 = one of Cell 1.

Thanks for all your help on this; I'm really sorry that I posted the numbering wrong.

**Wheres_the_Red** · 04-24-2007, 08:57 PM

Thanks for providing the upper limit. What would be in Cell1 if 999,999,999 was keyed?? Are all cells to hold a maximum of two digits?? or can Cell1 hold 3? If 2, how does the 999mill get laid out??

James

**Paul** · 04-24-2007, 10:13 PM

To me, it looks like you're simply taking the rightmost two digits and inserting them in the 4th cell, then the next two rightmost digits into the 3rd cell, the next two into the 2nd cell, and the last two into the first cell (if they exist).

It doesn't look like any math or logic is involved. Plus, based on your example (and as Red questioned), your 4 cells cannot handle a number over 99,999,999 (not 999,999,999 as you suggest). If it goes from 100 to 10,000 to 1,000,000 then the next step would be 100,000,000 - requiring a 5th cell that could handle numbers up to 9,999,999,999.

If I'm on the right track.. let's say your four cells are A1, A2, A3, A4, and your number is in cell B1, then use these formulas in A1 through A4 (in order):

A1 =IF(LEN(B1)<7,"",RIGHT(LEFT(B1,LEN(B1)-6),2))
A2 =IF(LEN(B1)<5,"",RIGHT(LEFT(B1,LEN(B1)-4),2))
A3 =IF(LEN(B1)<3,"",RIGHT(LEFT(B1,LEN(B1)-2),2))
A4 =RIGHT(B1,2)

**Wheres_the_Red** · 04-24-2007, 11:15 PM

Not arguing with PJ's method,

. . . .
but it can be done with a degree of logic/math, as suggested originally by Leith.

See the attached file, especially Sample 4. Sample 5 takes it one step further, introducing Base10 exponential, which allows you to specify how many digits should appear in the cells (assuming same number of digits in each cell).

James

Split Numbers.zip