How to separate numbers within a cells into column

A

Alvyn

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
 
G

Gary''s Student

If your value is in B1, then in A1 enter:

=MID($A2,COLUMN(),1) and copy across
 
A

Alvyn

Hi Gary,

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

Thanks
 
V

Vulcan

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))
 
G

Gord Dibben

Try again.

12345 in A2

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

Copy across to E1


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top