How do I extract the last line from a cell?

Hello! I am trying to extract the last line from a cell and place it in a new column marked as "Current Status." Ideally, I want "This vendor is going to be retired by Nov 2022." in the Current Status column.

I can do this in excel using the below formula. any help is appreciated. thank you!

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))



Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Ahmed Iqbal

    This will always find the last last line break of your cell and return the line, regardless of what text is written or how many lines you have.

    =RIGHT([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - FIND("~", SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "~", LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - LEN(SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "")))))

    The "~" is there to give you an unusual placeholder. If this character could show up in your text, choose another unusual symbol

    The LEN(your column)-LEN(Substitute([your column]...) calculates how many line breaks are in the string. Think of this as a COUNTIF function- and tells the substitute function which of the line breaks it should act on. Because it is giving you the total count, it is this term that always finds the last line break.

    If the formula gives an unparseable then first double check my typing/spelling of your column name.

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Ahmed Iqbal

    This will always find the last last line break of your cell and return the line, regardless of what text is written or how many lines you have.

    =RIGHT([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - FIND("~", SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "~", LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - LEN(SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "")))))

    The "~" is there to give you an unusual placeholder. If this character could show up in your text, choose another unusual symbol

    The LEN(your column)-LEN(Substitute([your column]...) calculates how many line breaks are in the string. Think of this as a COUNTIF function- and tells the substitute function which of the line breaks it should act on. Because it is giving you the total count, it is this term that always finds the last line break.

    If the formula gives an unparseable then first double check my typing/spelling of your column name.

    Does this work for you?

    Kelly

  • Hi Kelly,

    Thank you for the prompt response. This helped a lot!

    Ahmed Iqbal

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!