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

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

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
Categories
Check out the Formula Handbook template!