How to separate numbers within a cells into column

  • Thread starter Thread starter Alvyn
  • Start date Start date
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
 
Hi Gary,

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

Thanks
 
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))
 
Try again.

12345 in A2

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

Copy across to E1


Gord Dibben MS Excel MVP
 
Back
Top