Formula to Display Specific Line of Text in a Cell

[meppwc]

I have the need to have a formula in B1 that looks at the contents of A1 and then displays a specific line of text that exists in A1
For Example:
A1 has the following:
REPLACE & LOANER
NEW CAD = 04/19
DELIVERY ABORTED 04/08 ON LOANER

I want cell B1 to display the contents of line 3 - "NEW CAD = 04/19"
To note. Upon creation of A1, these line are not wrapped. I seperate each line using Alt-Enter

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1
2	REPLACE & LOANER NEW CAD = 04/19 DELIVERY ABORTED 04/08 ON LOANER	NEW CAD = 04/19
Main

Cell Formulas
Range	Formula
B2	B2	=LET(a,REPLACE(A2,1,FIND(CHAR(10),A2),""),REPLACE(a,FIND(CHAR(10),a),LEN(a),""))

 

[meppwc]

Thanks Fluff.............I think we are on to something. This formula successfully displayed line 2 of cell A1
To be sure I understand the formula and how to change it for other line #'s, can you provide the same formula where as it will display line 3 ?

 

[Fluff]

How about

=LET(a,REPLACE(A2,1,FIND("^",SUBSTITUTE(A2,CHAR(10),"^",2)),""),REPLACE(a,FIND(CHAR(10),a&CHAR(10)),LEN(a),""))

 

[meppwc]

Thanks again...............I am going to have to study this much harder to understand it.
My hope is to apply it with up to 6 lines of information.
I very much appreciate what you have provided.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[meppwc]

Fluff, can I impose on your expertise one more time in relation to this? I was successful with adapting the formula to work for line 2 on down, but I have yet to figure out a way to display the 1st line. Would you mind assisting me with that?

 

[jtakw]

Hi,

A single formula option for as many lines as you want:

Book3.xlsx
	A	B	C	D	E	F	G
1	REPLACE & LOANER NEW CAD = 04/19 DELIVERY ABORTED 04/08 ON LOANER	REPLACE & LOANER	NEW CAD = 04/19	DELIVERY ABORTED 04/08 ON LOANER
2	REPLACE & LOANER NEW CAD = 04/19 DELIVERY ABORTED 04/08 ON LOANER ADFJKASDJFK 456 678J SIEHF;ALS 23445678	REPLACE & LOANER	NEW CAD = 04/19	DELIVERY ABORTED 04/08 ON LOANER	ADFJKASDJFK 456	678J SIEHF;ALS	23445678
Sheet1085

Cell Formulas
Range	Formula
B1:G2	B1	=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",300)),COLUMNS($B1:B1)*300-299,300))

 

[meppwc]

Oh that is really cool...............thanks !!!

 

[Fluff]

If you are trying to split the text then you could also use

=LET(Txt,CHAR(10)&A2&CHAR(10),Qty,SEQUENCE(,(LEN(Txt)-LEN(SUBSTITUTE(Txt,CHAR(10),"")))-1),UNIQUE(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,CHAR(10),"~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,CHAR(10),"~",Qty)),"")))

which is more robust than the method jtakw used.