CODEWITHSUNDEEP
excelextractformula
Gurdeep
Gurdeep

Reputation: 55

Extract last line in excel cell

I have a column in my file where each cell includes some text in the following format:

dd/mm/yyyy: blah blah blah

dd/mm/yyyy: blah blah blah

dd/mm/yyyy: blah blah blah

I want to be able to extract the last comment from this cell (to see when it was last updated). I've tried to use the following formula 

IF(ISERROR(RIGHT(H447,FIND(CHAR(10),H447))),H447,RIGHT(H447,FIND(CHAR(10),H447)))

which I thought would in theory show me everything onwards from the last line break, however it doesn't. It would show me, taking the above example 

lah blahdd/mm/yyyy: blah blah blah

I can't figure out why this isn't working correctly. Or is the only way to do this realistically is to use macros?

Upvotes: 0

Views: 1673

Answers (1)

OldUgly
OldUgly

Reputation: 2119

First, see this post on determining the last occurrence of a character in a string.

The following formula will accomplish what you are after ...

=IF(ISERROR(RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,CHAR(10),"@",(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))/LEN(CHAR(10)))))),A1,RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,CHAR(10),"@",(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))/LEN(CHAR(10))))))

Pretty long, but effective ...

enter image description here

Upvotes: 2

Related Questions