Last line of a cell

[Mr_Phil]

Since you can force a break with alt-enter in a cell; what would be the easiest way to capture the last line of that cell for use in a "summary" sheet.

Example: Cell F1 is NOTES and in F2 we find --

ISSUE IDENTIFIED 9/19/17
NOT DONE AS OF 9/28/17
NOT DONE / MESSAGE TO OWNER 10/05/17
ISSUE NOT DONE 2ND MESSAGE TO OWNER 10/13/17
INSPECTION SCHEDULED 10/24/17

The summary sheet would like to capture the last line which in the above example would be "INSPECTION SCHEDULED 10/24/17".

The reason it is all one cell is that the row uses an equipment id number and other unique items and dedicating an entire row to duplicate everything except the last row of a single cell would make for a huge worksheet. I've tried the obvious =right() function. But I cannot settle on a string length that consistently captures ONLY the last row of the cell. The only certainty is that the date entered will be the last item on each line.

Thanks for reading.

[Norie]

Try this.

=MID(F2,SEARCH("^^",SUBSTITUTE(F2,CHAR(10),"^^",LEN(F2)-LEN(SUBSTITUTE(F2,CHAR(10),"")))),LEN(F2))

[Phuocam]

Or try this ...

=TRIM(RIGHT(SUBSTITUTE(F2,CHAR(10),REPT(" ",LEN(F2))),LEN(F2)))

[AlKey]

Or

Please Login or Register  to view this content.

v	F
1
2	ISSUE IDENTIFIED 9/19/17 NOT DONE AS OF 9/28/17 NOT DONE / MESSAGE TO OWNER 10/05/17 ISSUE NOT DONE 2ND MESSAGE TO OWNER 10/13/17 INSPECTION SCHEDULED 10/24/17
3	INSPECTION SCHEDULED 10/24/17
4

[Mr_Phil]

All of these hit the spot thank you to each of you. Problem solved.

I've never seen "^^" used in a formula before and I'm sort of fuzzy on the LEN and SUBSTITUTE functions. So, I am going to have to try to learn them well enough to apply them on my own.
Ditto the REPT function.
Looks like I have some homework.
Many thanks.

Try this.

=MID(F2,SEARCH("^^",SUBSTITUTE(F2,CHAR(10),"^^",LEN(F2)-LEN(SUBSTITUTE(F2,CHAR(10),"")))),LEN(F2))

Or try this ...

=TRIM(RIGHT(SUBSTITUTE(F2,CHAR(10),REPT(" ",LEN(F2))),LEN(F2)))

Or

Please Login or Register  to view this content.

v	F
1
2	ISSUE IDENTIFIED 9/19/17 NOT DONE AS OF 9/28/17 NOT DONE / MESSAGE TO OWNER 10/05/17 ISSUE NOT DONE 2ND MESSAGE TO OWNER 10/13/17 INSPECTION SCHEDULED 10/24/17
3	INSPECTION SCHEDULED 10/24/17
4