Thread: splitting a single number into multiple columns

Hi friends,
Suppose there is a number 223 and i want to split it into different columns col1-2,col2-2,col3-3

like
If A1=223
then i want
B1=2
B2=2
B3=3

and i dont want any change in A1

Is there any way to do that--

=MID(REPT("0",3-LEN($A$1)) & $A$1,ROW(),1)

would allow for different sises of numbers, change 3 to the maximum digits in your number, if you want blank cells rather than leading zeros replace the "0" with " "

Try this:

=IF(COLUMN(E12)-4<=LEN($D$12);MID($D$12;COLUMN(E12)-4;1);"")

Where D12 is number
E12 is first formula (extend other to right)
COLUMN(E12)-4 is to get 1 first number... so if you put other column e.g. B you must take down other number A12 - 0, B12-1, C12-2, D12-3, E12-4, F12-5 etc

Sorry, didn't wathc you going down

=IF(ROW(B1)<=LEN($A$1);MID($A$1;ROW(B1);1);"")

in B1 to as many as you need characters

Book1.xls

deleted again i misread the question doh!
but you say

col1-2,col2-2,col3-3

which is across columns
and contradict that with
b1 2
b2 2
b3 3
which is down rows,
did you mean A1 223 B1 2 C1 2 D1 3

what you mean by other rows? As I explain above, if you moving to other rows (as for toher columns) you must adjust formula to look to first row.

This was for B1.

If you putting formula to K823

you must adjust formula to look at ROW(K823)-822

no
That's why we put in formula ROW(c500)-499

So you don't need to change it...

You should change if you put 0 in first, -1 in second, -2 in third..... -499 in 500th formula...

With ROW you got it automatic, you just need to adopt it to the FIRST formula