Hi, I'm struggling with finding the right formula to use. I have a cell that contains a great deal of information. I want to pull the date and time after the word STOP in the below example. As you can see the word STOP appears twice and so I would need to pull the date and time after each word STOP. My return should be 01/06 11:03 01/07 10:29. Nothing I have tried seems to do the trick. Any help would be hugely appreciated.
| STOP@01/06 11:03 (USER96639) FLAT[08]@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) FLAT[08]@01/07 10:29 USER(USER96639) |
If you have the new LET function how about
=LET(Qty,(LEN(A2)-LEN(SUBSTITUTE(A2,"STOP@","")))/5, Sub,SUBSTITUTE(A2,"STOP@","|",SEQUENCE(2,,Qty-1)),TEXTJOIN(CHAR(10),1,MID(Sub,FIND("|",Sub)+1,11)))
This will find the last two STOP@ as requested here
https://www.mrexcel.com/board/threads/i-need-to-pull-the-time-and-date-from-a-cell.1157960/
Hi Ellen
You can give this a try
=IF(LEFT(A1,4)="STOP",MID(A1,FIND("@",A1)+1,11),A1)
or maybe
=IF(LEFT(A1,4)="STOP",SUBSTITUTE(SUBSTITUTE(A1,"STOP@",""),"(USER96639)",""),A1)
