Split List in Cell, Data input via [Alt-RET]-Delimiter Fails.

[Roj47]

Hi,

I have a list of data (up to 10 lines) in a single cell and I am trying to split this list so they appear in unique cells.

Normally I would use the Text to Column BUT..., having used [ALT-RET] to drop down to enter the next data, the Test to Column only returns the first item in the list and deleted the remaining.

Is there a way round this?

I have also pasted the cell so it loses the [ALT-RET] part so the data is in a continuous string but still only returns the first line.

Data in the cell:

Apples-Red,
Apples-Green,
Peppers-Red,
Peppers-Green,
Peppers-Yellow.

Thanks.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1
2	Apples-Red, Apples-Green, Peppers-Red, Peppers-Green, Peppers-Yellow.	Apples-Red,	Apples-Green,	Peppers-Red,	Peppers-Green,	Peppers-Yellow.
Data

Cell Formulas
Range	Formula
B2:F2	B2	=TEXTSPLIT(A2,CHAR(10))
Dynamic array formulas.

 

[Roj47]

Thank you.

Works perfectly - No more [Alt-RET] use for me from here.

I'll drop a thumbs up/ close; I am curious what the CHAR(10) means please? I thought perhaps it sought the symbol ',' but I have '.' in there also.

Apologies if expanding explanations isn;t good etiquette.

 

[Fluff]

The Char(10) is the newline character (ie Alt enter)

 

[kvsrinivasamurthy]

Book1.xlsm
	A	B	C	D	E	F
1
2	aaa bbb fff	aaa	bbb	fff
3	aaa bbb fff ppp	aaa	bbb	fff	ppp
4	aaa bbb fff	aaa	bbb	fff
5	aaa bbb	aaa	bbb
6	aaa bbb fff	aaa	bbb	fff
7
Sheet1

Data A2:A6.
Formula in B2

=LET(a,A2:A6,b,BYROW(a,LAMBDA(r,SUBSTITUTE(r,CHAR(10),"_"))),c,TEXTSPLIT(TEXTJOIN(",",FALSE,b),"_",",",0,1,""),c)