How do I extract the last line from a cell?

Ahmed Iqbal

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))

Kelly Moore

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

Kelly Moore

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

Ahmed Iqbal

Hi Kelly,

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

Ahmed Iqbal