Extract individual lines from cell

[SaraWitch]

Hello peeps,

I have a cell with as many as six lines of data and want to extract values line by line. I have a formula to extract the first line and everything after the first line, but this is returning all five lines. Is there a way to extract just line 2, line 3, line 4...etc.?

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Addresses	Date ended	Address 1	Date from	Date to		Address 2	Would like	Date from	Date to		Address 3	Would like	Date from	Date to		Address 4	Would like	Date from	Date to		Address 5	Would like	Date from	Date to		Address 6	Would like	Date from	Date to
2	Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	04/01/2024	Gnome Cave, High Cloud - 25/08/2022	25/08/2022	30/12/2023		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Sky Apartments, Stormville - 08/08/2022	08/08/2022	31/12/2023		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Low Heaven, Angel Cove - 06/01/2022	08/08/2022	01/01/2024		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Ocean Whirlpool, Seagate - 10/10/2023	08/08/2022	02/01/2024		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Valley Deep, Grasslands - 11/11/2023	08/08/2022	03/01/2024		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Forest Mound, Woodland - 02/01/2024	08/08/2022	04/01/2024
Extract lines

Cell Formulas
Range	Formula
C2	C2	=TEXTBEFORE($A2,CHAR(10))
D2	D2	=IFERROR(DATEVALUE(MID(C2,SEARCH("/??/",C2,1)-2,10)),"")
E2,T2,O2,J2	E2	=IF(I2="",$B2,J2-1)
G2,AA2,V2,Q2,L2	G2	=TEXTAFTER($A2,CHAR(10))
I2,AC2,X2,S2,N2	I2	=IFERROR(DATEVALUE(MID(G2,SEARCH("/??/",G2,1)-2,10)),"")
Y2	Y2	=IF(X2="","",IF(AC2>0,AD2-1,$B2))
AD2	AD2	=IF(AC2="","",IF(AG2>0,AH2-1,$B2))

Any help would be appreciated

 

[SaraWitch]

I've also noticed that if I only have five lines of text, my date formula creates errors...

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Addresses	Date ended	Address 1	Address 1	Date from	Date to		Address 2	Would like	Date from	Date to		Address 3	Would like	Date from	Date to		Address 4	Would like	Date from	Date to		Address 5	Would like	Date from	Date to		Address 6	Would like	Date from	Date to
2	Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	04/01/2024	Gnome Cave, High Cloud - 25/08/2022	Gnome Cave, High Cloud - 25/08/2022	25/08/2022	30/12/2023		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Sky Apartments, Stormville - 08/08/2022	08/08/2022	31/12/2023		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Low Heaven, Angel Cove - 06/01/2022	08/08/2022	01/01/2024		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Ocean Whirlpool, Seagate - 10/10/2023	08/08/2022	02/01/2024		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Valley Deep, Grasslands - 11/11/2023	08/08/2022	03/01/2024		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	Forest Mound, Woodland - 02/01/2024	08/08/2022	04/01/2024
3	Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023	04/01/2024	Gnome Cave, High Cloud - 25/08/2022	Gnome Cave, High Cloud - 25/08/2022	25/08/2022	#VALUE!		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023	Sky Apartments, Stormville - 08/08/2022	08/08/2022	#VALUE!		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023	Low Heaven, Angel Cove - 06/01/2022	08/08/2022	#VALUE!		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023	Ocean Whirlpool, Seagate - 10/10/2023	08/08/2022	#VALUE!		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023	Valley Deep, Grasslands - 11/11/2023	08/08/2022	#VALUE!		Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023
Extract lines

Cell Formulas
Range	Formula
C2:D3	C2	=TEXTBEFORE($A2,CHAR(10))
E2:E3,AD2,Y2:Y3,T2:T3,O2:O3,J2:J3	E2	=IFERROR(DATEVALUE(MID(C2,SEARCH("/??/",C2,1)-2,10)),"")
F2:F3,U2:U3,P2:P3,K2:K3	F2	=IF(J2="",$B2,K2-1)
Z2:Z3	Z2	=IF(Y2="","",IF(AD2>0,AE2-1,$B2))
AE2:AE3	AE2	=IF(AD2="","",IF(AH2>0,AI2-1,$B2))
AD3	AD3	=IFERROR(DATEVALUE(MID(AC3,SEARCH("/??/",AC3,1)-2,10)),"")
H2:H3,AB2:AB3,W2:W3,R2:R3,M2:M3	H2	=TEXTAFTER($A2,CHAR(10))

 

[Scott Huish]

You could use INDEX and TEXTSPLIT
=INDEX(TEXTSPLIT($A2,CHAR(10)),1)
=INDEX(TEXTSPLIT($A2,CHAR(10)),2)
=INDEX(TEXTSPLIT($A2,CHAR(10)),3)
=INDEX(TEXTSPLIT($A2,CHAR(10)),4)
=INDEX(TEXTSPLIT($A2,CHAR(10)),5)

 

[Scott Huish]

What is the Date supposed to be if there isn't 5 values? 1 day earlier than the last date?

 

[SaraWitch]

Wow, that's fabulous! Thank you, Scott.

I've updated the table with a few more examples and put what the dates should be:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Addresses	Date ended	Address 1		Date from	Date to	Date to should be	Address 2		Date from	Date to	Date to should be	Address 3		Date from	Date to	Date to should be	Address 4		Date from	Date to	Date to should be	Address 5		Date from	Date to	Date to should be	Address 6		Date from	Date to	Date to should be
2	Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024	10/01/2024	Gnome Cave, High Cloud - 25/08/2022	Gnome Cave, High Cloud	25/08/2022	05/01/2024	05/01/2024	Sky Apartments, Stormville - 08/08/2022	Sky Apartments, Stormville	08/08/2022	06/01/2024	06/01/2024	Low Heaven, Angel Cove - 06/01/2022	Low Heaven, Angel Cove	06/01/2022	07/01/2024	07/01/2024	Ocean Whirlpool, Seagate - 10/10/2023	Ocean Whirlpool, Seagate	10/10/2023	08/01/2024	08/01/2024	Valley Deep, Grasslands - 11/11/2023	Valley Deep, Grasslands	11/11/2023	09/01/2024	09/01/2024	Forest Mound, Woodland - 02/01/2024	Forest Mound, Woodland	02/01/2024	10/01/2024	10/01/2024
3	Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023	10/01/2024	Gnome Cave, High Cloud - 25/08/2022	Gnome Cave, High Cloud	25/08/2022	#VALUE!	06/01/2024	Sky Apartments, Stormville - 08/08/2022	Sky Apartments, Stormville	08/08/2022	#VALUE!	07/01/2024	Low Heaven, Angel Cove - 06/01/2022	Low Heaven, Angel Cove	06/01/2022	#VALUE!	08/01/2024	Ocean Whirlpool, Seagate - 10/10/2023	Ocean Whirlpool, Seagate	10/10/2023	#VALUE!	09/01/2024	Valley Deep, Grasslands - 11/11/2023	Valley Deep, Grasslands	11/11/2023	#VALUE!	10/01/2024					BLANK CELL
4	Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023	10/01/2024	Gnome Cave, High Cloud - 25/08/2022	Gnome Cave, High Cloud	25/08/2022	#VALUE!	07/01/2024	Sky Apartments, Stormville - 08/08/2022	Sky Apartments, Stormville	08/08/2022	#VALUE!	08/01/2024	Low Heaven, Angel Cove - 06/01/2022	Low Heaven, Angel Cove	06/01/2022	#VALUE!	09/01/2024	Ocean Whirlpool, Seagate - 10/10/2023	Ocean Whirlpool, Seagate	10/10/2023	#VALUE!	10/01/2024					BLANK CELL					BLANK CELL
5	Gnome Cave, High Cloud - 25/08/2022	10/01/2024	Gnome Cave, High Cloud - 25/08/2022	Gnome Cave, High Cloud	25/08/2022	#VALUE!	10/01/2024					BLANK CELL					BLANK CELL					BLANK CELL					BLANK CELL					BLANK CELL
Extract lines

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),1),"")
D2:D5	D2	=IFERROR(LEFT(A2,FIND("-",A2)-1),"")
E2:E5,AD2,Y2:Y5,T2:T5,O2:O5,J2:J5	E2	=IFERROR(DATEVALUE(MID(C2,SEARCH("/??/",C2,1)-2,10)),"")
F2:F5,Z2:Z5,U2:U5,P2:P5	F2	=IF(E2="","",IF(J2>0,K2-1,$B2))
H2:H5	H2	=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),2),"")
I2:I5,AC2:AC5,X2:X5,S2:S5,N2:N5	I2	=IFERROR(LEFT(H2,FIND("-",H2)-1),"")
K2:K5	K2	=IF(J2="","",IF(P2>0,Q2-1,$B2))
M2:M5	M2	=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),3),"")
R2:R5	R2	=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),4),"")
W2:W5	W2	=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),5),"")
AB2:AB5	AB2	=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),6),"")
AE2:AE5	AE2	=IF(AD2="","",$B2)
AD3:AD5	AD3	=IFERROR(DATEVALUE(MID(AC3,SEARCH("/??/",AC3,1)-2,10)),"")

 

[SaraWitch]

In F3, I did also try:

=IF(E2=0,"",IF(J2>0,K2-1,$B2))

...but that's not working either...

 

[SaraWitch]

Sorted! I used:

=IF(E2="","",IF(J2="",$B2,K2-1))