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

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

=MID($A2,COLUMN(),1) and copy across
--
Gary''s Student - gsnu200787

Sorry


I should have said the value nust be in A2, not B1
--
Gary''s Student - gsnu200787


"Alvyn" wrote:

> 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

"Gary''s Student" wrote:

> If your value is in B1, then in A1 enter:
>
> =MID($A2,COLUMN(),1) and copy across
> --
> Gary''s Student - gsnu200787

Dear Gary,

It does not work as desire.

Thanks


"Gary''s Student" wrote:

> Sorry
>
>
> I should have said the value nust be in A2, not B1
> --
> Gary''s Student - gsnu200787
>
>
> "Alvyn" wrote:
>
> > 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

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



"Alvyn" <Alvyn@discussions.microsoft.com> wrote in message
news:734FCE46-64B2-497E-8572-81EE89A8D2CC@microsoft.com...
> Dear Gary,
>
> It does not work as desire.
>
> Thanks
>
>
> "Gary''s Student" wrote:
>
>> Sorry
>>
>>
>> I should have said the value nust be in A2, not B1
>> --
>> Gary''s Student - gsnu200787
>>
>>
>> "Alvyn" wrote:
>>
>> > 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

Try again.

12345 in A2

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

Copy across to E1


Gord Dibben MS Excel MVP

On Thu, 22 May 2008 10:25:01 -0700, Alvyn <Alvyn@discussions.microsoft.com>
wrote:

>Dear Gary,
>
>It does not work as desire.
>
>Thanks
>
>
>"Gary''s Student" wrote:
>
>> Sorry
>>
>>
>> I should have said the value nust be in A2, not B1
>> --
>> Gary''s Student - gsnu200787
>>
>>
>> "Alvyn" wrote:
>>
>> > 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

It should work for both numbers AND text.
--
Gary''s Student - gsnu200787


"Vulcan" wrote:

> 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))
>
>
>
> "Alvyn" <Alvyn@discussions.microsoft.com> wrote in message
> news:734FCE46-64B2-497E-8572-81EE89A8D2CC@microsoft.com...
> > Dear Gary,
> >
> > It does not work as desire.
> >
> > Thanks
> >
> >
> > "Gary''s Student" wrote:
> >
> >> Sorry
> >>
> >>
> >> I should have said the value nust be in A2, not B1
> >> --
> >> Gary''s Student - gsnu200787
> >>
> >>
> >> "Alvyn" wrote:
> >>
> >> > 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
>

Ya, it should, but it doesn't, depending on how the original cell is
formatted

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:F5BA40FB-4719-46CD-B95B-7BCD15E2D6BC@microsoft.com...
> It should work for both numbers AND text.
> --
> Gary''s Student - gsnu200787
>
>
> "Vulcan" wrote:
>
>> 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))
>>
>>
>>
>> "Alvyn" <Alvyn@discussions.microsoft.com> wrote in message
>> news:734FCE46-64B2-497E-8572-81EE89A8D2CC@microsoft.com...
>> > Dear Gary,
>> >
>> > It does not work as desire.
>> >
>> > Thanks
>> >
>> >
>> > "Gary''s Student" wrote:
>> >
>> >> Sorry
>> >>
>> >>
>> >> I should have said the value nust be in A2, not B1
>> >> --
>> >> Gary''s Student - gsnu200787
>> >>
>> >>
>> >> "Alvyn" wrote:
>> >>
>> >> > 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
>>