From: Elton Law on
Super expert,

If I have cell containing a series of data like this ....

1,12,9
1,2,9
1,22,17,18
23,23,1,9
24,21,1
1,23,11
22,1
2,3


Is it possible to use function or command to split them into columns?

1 12 9
1 2 9
1 22 17 18
23 23 1 9
24 21 1
1 23 11
22 1
2 3

I don't want to use "TEXT TO COLUMN" as some of the addresses can be
overwritten.

Thanks so much,
Regards,
Elton
From: Jackpot on
Hi 'Elton Law'

Try the below

=TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",",
REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))


"Elton Law" wrote:

> Super expert,
>
> If I have cell containing a series of data like this ....
>
> 1,12,9
> 1,2,9
> 1,22,17,18
> 23,23,1,9
> 24,21,1
> 1,23,11
> 22,1
> 2,3
>
>
> Is it possible to use function or command to split them into columns?
>
> 1 12 9
> 1 2 9
> 1 22 17 18
> 23 23 1 9
> 24 21 1
> 1 23 11
> 22 1
> 2 3
>
> I don't want to use "TEXT TO COLUMN" as some of the addresses can be
> overwritten.
>
> Thanks so much,
> Regards,
> Elton
From: Elton Law on
Oh.. you are really super expert.
Thanks so much. Thanks ... that's really helpful

"Jackpot" wrote:

> With data in cell A1; apply the below formula in cell B1 and copy to the
> right as required..
>
> "Jackpot" wrote:
>
> > Hi 'Elton Law'
> >
> > Try the below
> >
> > =TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",",
> > REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))
> >
> >
> > "Elton Law" wrote:
> >
> > > Super expert,
> > >
> > > If I have cell containing a series of data like this ....
> > >
> > > 1,12,9
> > > 1,2,9
> > > 1,22,17,18
> > > 23,23,1,9
> > > 24,21,1
> > > 1,23,11
> > > 22,1
> > > 2,3
> > >
> > >
> > > Is it possible to use function or command to split them into columns?
> > >
> > > 1 12 9
> > > 1 2 9
> > > 1 22 17 18
> > > 23 23 1 9
> > > 24 21 1
> > > 1 23 11
> > > 22 1
> > > 2 3
> > >
> > > I don't want to use "TEXT TO COLUMN" as some of the addresses can be
> > > overwritten.
> > >
> > > Thanks so much,
> > > Regards,
> > > Elton
From: Roger Govier on
Hi Elton

Mark your block of data>Data>Text to columns>Delimited>select Comma as
delimited>Finish

--

Regards
Roger Govier

"Elton Law" <EltonLaw(a)discussions.microsoft.com> wrote in message
news:471706BF-00B0-426C-AEF1-9D9B11813937(a)microsoft.com...
> Super expert,
>
> If I have cell containing a series of data like this ....
>
> 1,12,9
> 1,2,9
> 1,22,17,18
> 23,23,1,9
> 24,21,1
> 1,23,11
> 22,1
> 2,3
>
>
> Is it possible to use function or command to split them into columns?
>
> 1 12 9
> 1 2 9
> 1 22 17 18
> 23 23 1 9
> 24 21 1
> 1 23 11
> 22 1
> 2 3
>
> I don't want to use "TEXT TO COLUMN" as some of the addresses can be
> overwritten.
>
> Thanks so much,
> Regards,
> Elton
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5170 (20100603) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 5170 (20100603) __________

The message was checked by ESET Smart Security.

http://www.eset.com



From: Ms-Exl-Learner on
Jacob Sir small correction is required.

=TRIM(MID(SUBSTITUTE("," & INDIRECT(ADDRESS(ROW(),1))&
REPT(",",6),",",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))

May I know the reason, why you have changed your name? When I see the
formula I guessed the formula should be provided by the real experts like
you. But here I am trying to get the result for more than half an hour but I
can't able to make it in single formula. Today I have learned 1 more from
your post.

--------------------
(Ms-Exl-Learner)
--------------------


"Jackpot" wrote:

> Hi 'Elton Law'
>
> Try the below
>
> =TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",",
> REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))
>
>
> "Elton Law" wrote:
>
> > Super expert,
> >
> > If I have cell containing a series of data like this ....
> >
> > 1,12,9
> > 1,2,9
> > 1,22,17,18
> > 23,23,1,9
> > 24,21,1
> > 1,23,11
> > 22,1
> > 2,3
> >
> >
> > Is it possible to use function or command to split them into columns?
> >
> > 1 12 9
> > 1 2 9
> > 1 22 17 18
> > 23 23 1 9
> > 24 21 1
> > 1 23 11
> > 22 1
> > 2 3
> >
> > I don't want to use "TEXT TO COLUMN" as some of the addresses can be
> > overwritten.
> >
> > Thanks so much,
> > Regards,
> > Elton
 |  Next  |  Last
Pages: 1 2
Prev: Import external data
Next: How do I unhide collom a?