0

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?

Gurdeep
  • 55
  • 1
  • 11
  • The formula seems to work for me. – OldUgly Apr 27 '16 at 08:38
  • Although this is slightly better, because it strips the last line feed ... `=IF(ISERROR(RIGHT(A1,FIND(CHAR(10),A1)-1)),A1,RIGHT(A1,FIND(CHAR(10),A1)-1))` (for testing I put the contents in`A1`, not `H447` – OldUgly Apr 27 '16 at 08:40
  • It doesn't work when the length of the individual lines are different. The FIND finds the first CHAR(10) when you want to find the last CHAR(10). – OldUgly Apr 27 '16 at 08:49
  • yeah, that's the problem - each of the lines are a different length. I thought using RIGHT() would start the find process from the end to the start, and so picking up the last line break? Would an alternate be someway to find [line break] AND xx/xx/xxxx? Not sure how to go about that though – Gurdeep Apr 27 '16 at 08:58

1 Answers1

2

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

Community
  • 1
  • 1
OldUgly
  • 2,129
  • 3
  • 13
  • 21
  • That works really well, I've never seen substitute used like that, really clever. Thank you! – Gurdeep Apr 27 '16 at 09:13
  • @OldUgly Can you walk through what your formula is doing? I'm not exactly a super user and I'd love to be able to modify what you've got going on there to do something like pull the next to last line. – I Dabble May 31 '17 at 14:50