How to give a string of data into different columns

[Elton Law]

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

 

[Jackpot]

Hi 'Elton Law'

Try the below

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

 

[Jackpot]

With data in cell A1; apply the below formula in cell B1 and copy to the
right as required..

 

[Elton Law]

Oh.. you are really super expert.
Thanks so much. Thanks ... that's really helpful

 

[Roger Govier]

Hi Elton

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

--

Regards
Roger Govier

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

 

[Ms-Exl-Learner]

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.

 

[Jackpot]

Thanks mate..or change $A$1 to $A1

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]

Wow!!! Unnecessarily I have used Indirect, Address & Row functions. I am
laughing myself for my correction method.

 

[Teethless mama]

Text to column > select comma as your delimited

if you preferred formula then try this:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),(COLUMN(A$1)-1)*99+1,99))

copy across and down