CODEWITHSUNDEEP
excel
zzz409
zzz409

Reputation: 3

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

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.

Upvotes: 0

Views: 3878

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

If your text is in A1, try:

=LEFT(A1;FIND("CODE: ";A1)-1)

enter image description here

UPDATE: If you do -2 instead of -1, you will get rid of the last line break too (actually the image is with -2 instead of -1)

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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.

enter image description here

If you want to replace it in the same cell, you will need VBA

Upvotes: 3

Related Questions