How do I delete the last line of a multi-line cell

Question

I have an Excel file with roughly 4000 rows of multi-line cells and I need to delete last line of each cell.

All cells are formatted like this - also last line of each cell begins with the same word (CODE:) but the length can vary:

this is the first line
this is the second line
this is the third line
CODE: 123456789

To:

this is the first line
this is the second line
this is the third line

I've also tried replacing line breaks with the "|" symbol and using a formula that deletes everything after the last "|" but had no success and pretty much every formula I've found returns an error in Excel.

this is the first line|this is the second line|this is the third line|CODE:123456789

To:

this is the first line|this is the second line|this is the third line

Multi-line or with symbol doesn't matter - either way works for me.

Marked as duplicate, use the code in the link to find the position of the last "|" then use mid to select everything up to that character — JeffUK, May 03 '19 at 11:08
Possible duplicate of [Excel: last character/string match in a string](https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string) — JeffUK, May 03 '19 at 11:08
Are you specifically looking for a formula or VBA solution or either? — SJR, May 03 '19 at 11:15
Excel 2013, I tried the formula JeffUK linked and it also returns an error. Looking for ANY solution, formula or VBA. — zzz409, May 03 '19 at 11:17

score 3 · Answer 1 · answered May 03 '19 at 11:19

To enter this in a new cell:

=REPLACE(A1,FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))),LEN(A1),"")

The formula replaces the last lf character with a rarely used character CHAR(1) and then replaces everything from that character to the end with a null string.

Be sure to enable wrap text on the new cell.