Extract last n lines from a cell

[SekharS]

Hello Experts,

I have a project tracker in which each row is represents an on-going project.
There is one Col-'Status Update', in which team adds a note, starting with a date and in a single line, what happened on that day.

I am looking for a formula to extract the last 3 lines in a cell from Col-A, into another cell in Col-B.
Please help.

Thank you in advance.
SekharS

 

[Luke M]

Formula in B2:
=TRIM(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",999),LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))-2),999,999))

Formula does assume there's at least 3 lines. If you need more robustness:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))>=3,
TRIM(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",999),LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))-2),999,999)),A2)

 

[SekharS]

Awesome Luke...
Can I ask for more

Thanks a ton.
Sekhar

 

[Luke M]

You're welcome, and thanks for the 'drink'!

 

[bosco_yip]

Another shorter formula option

=TRIM(RIGHT(SUBSTITUTE(A2,CHAR(10),REPT(" ",300)),1000))

Regards
Bosco

 

[Peter Bartholomew]

@Bosco
@Luke M
Nice piece of lateral thinking!

The final formula might be too short in that
=TRIM(RIGHT(SUBSTITUTE(A2,CHAR(10),REPT(" ",300)&CHAR(10)),1000))
would still give the line feeds as separators.

 

[Peter Bartholomew]

Just to demonstrate that totally different work practices are possible within Excel. This solution uses defined Names to create a programmatic approach.

Referring to the text as 'status', the current length 'n' is defined to refer to
= LEN(status)
Because Excel lacks a function to generate an index sequence, I define 'k' to be
= ROW( INDEX(Sheet1!C,1) : INDEX(Sheet1!C,n) )
where C is the active column (would be something like B:B in A1 notation).
The message string can then be broken into an array of individual characters 'chr'
= MID( status, k, 1 )
which allows the location 'break' of line feeds to be identified
= IF(chr=CHAR(10), k)
Of course we only require the third largest 'm' which is given by
= LARGE(break, 3)
Using that value we return to the original status text and extract the last three lines using
= MID(status, m+1, n-m)

Does it work? Feel free to examine the results (the worksheet includes the Luke/Bosco formula).

 

[SekharS]

Sorry, I could not thank you earlier.
Thank you bosco_yip & Peter Bartholomew. Appreciate your help.