Looking for a formula to insert "Alt+Enter" into a cell

[makamal]

Greetings to everyone,

How can I convert a cell contains data separated with a comma, like this:

S386006A,S386006B,S386006C,S386006D,S386006E,S386006F,S386006G,S386006H,S386006I,S386006J

To a cell with data similar to using "Alt+Enter" keys, like this:

S386006A
S386006B
S386006C
S386006D
S386006E
S386006F
S386006G
S386006H
S386006I
S386006J

Your immediate response will be highly appreciated. Thanks

 

[Fazza]

replace all commas via
CTRL-H
find what : ,
replace with : ALT then enter via the number pad 0010
ALT-A for replace all

hth

 

[cyrilbrd]

Given in A1:
[TABLE="width: 543"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]S386006A,S386006B,S386006C,S386006D,S386006E,S386006F,S386006G,S386006H,S386006I,S386006J[/TD]
[TD]S386006A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006J[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B1 is =TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&$A$1&",", ",", REPT(" ",255), ROW(2:2)), ",", REPT(" ",255), ROW(1:1)), 255, 255)) copied down till needed.

Would that work for you?

 

[Fazza]

Or this formula
=SUBSTITUTE(A1,",",CHAR(10))

 

[makamal]

Given in A1:
[TABLE="width: 543"]
<tbody>[TR]
[TD]S386006A,S386006B,S386006C,S386006D,S386006E,S386006F,S386006G,S386006H,S386006I,S386006J[/TD]
[TD]S386006A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S386006J[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B1 is =TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&$A$1&",", ",", REPT(" ",255), ROW(2:2)), ",", REPT(" ",255), ROW(1:1)), 255, 255)) copied down till needed.

Would that work for you?

Thanks for the prompt reply.
It works, but I want the result to be in one cell.

 

[makamal]

Or this formula
=SUBSTITUTE(A1,",",CHAR(10))

Wow.
This is it. This is what I was looking for.
Thanks a lot.

 

[Fazza]

you're welcome. regards