Removing all but last line of text from a Cell

    I have a spreadsheet that is imported from another software programme. One of the columns has a value which contains updates from users. I am only inerested in viewing the last comment in this cell and wondered if it would be possible to delete all the text from the Cell except for the last few (3 or 4) rows as this would then only leave the latest update.


    Thanks


    Nick

    Re: Removing all but last line of text from a Cell


    Try:
    =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))-0)))

    As written this returns the last line. Replace the 0 with a greater number to get more lines from the end. Unfortunately Excel suppresses the operation of the newline characters, so you end up with a continuous line of text. There may be a way around this but it eludes me at the moment.

    [EDIT}
    If you Format > Cell > Alignment > Text Control > Wrap text
    That will sort the newline supression.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login