How to give a string of data into different columns

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

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

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

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

Hi Elton

Mark your block of dataDataText to columnsDelimitedselect Comma as
delimitedFinish

--

Regards
Roger Govier

"Elton Law" wrote in message
...
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

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

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

"Ms-Exl-Learner" wrote:

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

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

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


"Jackpot" wrote:

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

"Ms-Exl-Learner" wrote:

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

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


"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