Excel - How to separate numbers within a cells into column

Asked By Alvy on 22-May-08 12:44 PM
Hi

I have search all the web but still cannot find a solution to this problem.

I have a single cell containing the number -> 12345

I wanted to either separate the number 1 into column A, 2 into Column B, 3
into Column C and so on.

Otherwise, if inserting a space between 1 and 2 and 3 and 4 and 5 is also
fine.

How to go about it?

Any EXCEL experts here can please advise me?

Thank you very much.

alvynng




GarysStuden replied on 22-May-08 12:53 PM
If your value is in B1, then in A1 enter:

=MID($A2,COLUMN(),1) and copy across
--
Gary''s Student - gsnu200787
GarysStuden replied on 22-May-08 12:57 PM
Sorry


I should have said the value nust be in A2, not B1
--
Gary''s Student - gsnu200787
Alvy replied on 22-May-08 01:00 PM
Hi Gary,

Can you please elaborate? or maybe explain more simply to a EXCEL nuts here.

Thanks
Alvy replied on 22-May-08 01:25 PM
Dear Gary,

It does not work as desire.

Thanks
Vulcan replied on 22-May-08 01:44 PM
Presumably you're original number, 12345 is a number and not text.  In order
for the above formula to work, you need to either format the number as text,
or use the text conversion in your formula.

Assuming you have the desired number is Colum A, Row 9 and you want it split
out into columns B9,C9,D9,E9 &F9 you could use:

=MID(TEXT($A9,"0"),COLUMN()-1,1)

in Colum B and copy to C,D,E&F

Now, the result will be text in those columns so if you want them to be
numbers, you need to add a function to convert them into numbers which would
look like this:

=VALUE(MID(TEXT($A9,"0"),COLUMN()-1,1))
Gord Dibben replied on 22-May-08 02:15 PM
Try again.

12345 in A2

In A1 enter =MID($A2,COLUMN(),1)

Copy across to E1


Gord Dibben  MS Excel MVP
GarysStuden replied on 22-May-08 02:24 PM
It should work for both numbers AND text.
--
Gary''s Student - gsnu200787
Vulcan replied on 23-May-08 06:17 PM
Ya, it should, but it does not, depending on how the original cell is
formatted
Tyler replied on 28-Aug-09 09:44 PM
You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.
skofiar kamberi replied to Tyler on 23-Aug-10 06:13 AM
can you help me





if i write a number in A2 i want that number to separate in B2,C2,D2,E2,F2...., this will repeat also in A3, but B, or C or D, colum must have different numbers, also a clolum can not contain number for example





A    B    C     D    E   F    G



5    1    3     2    5   4



5    2    1     4    3   5



5    4          5    1   2    3
skofiar kamberi replied to Tyler on 23-Aug-10 06:15 AM
can you help me





if i write a number in A2 i want that number to separate in B2,C2,D2,E2,F2...., this will repeat also in A3, but B, or C or D, colum must have different numbers, also a clolum can not contain number for example





A    B    C     D    E   F    G



5    1    3     2    5   4



5    2    1     4    3   5



5    4          5    1   2    3