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:

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

<colgroup><col><col></colgroup><tbody>
</tbody>

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:

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

<tbody>
</tbody>

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