How to give a string of data into different columns

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

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
 
Hi 'Elton Law'

Try the below

=TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",",
REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))
 
With data in cell A1; apply the below formula in cell B1 and copy to the
right as required..
 
Oh.. you are really super expert.
Thanks so much. Thanks ... that's really helpful
 
Hi Elton

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

--

Regards
Roger Govier

Elton Law said:
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
Click to expand...

__________ 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.
 
Thanks mate..or change $A$1 to $A1

Ms-Exl-Learner said:
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.
Click to expand...
 
Wow!!! Unnecessarily I have used Indirect, Address & Row functions. I am
laughing myself for my correction method.
 
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
 
Back
Top