Extract the Data from Cell with?

Question

I have been trying to extract the data from the cell and have created a below formula but its not working. I have attached a sample sheet for better understanding any help will be much appreciated.

If a cell has this word "COMPLETED" then formula will load just Completed other wise those strings which has ?.

=ArrayFormula(LEFT(A3,FIND("...DONE=>",A3)-1))

=TRIM(MID(A3,SEARCH($D$2,A3)+LEN($D$2),255))

https://docs.google.com/spreadsheets/d/1WjpvEfpndRJ-tTOF99mSF9knMyF5Sq_FVpxnnvbgHHg/edit?usp=sharing

Answer 1

Your solution here

=ArrayFormula(IFERROR(IFERROR(REGEXEXTRACT(A3:A,".* (COMPLETE) .*"),REGEXEXTRACT(A3:A,"^\*\*\* (.* )? "))))

You use REGEXEXTRACT and if not COMPLETE you use the REGEXEXTRACT.
And if cell is empty you use second IFERROR

Answer 2

Considering your example on the sheet, you can make use of Arrayformula, If, and REGEXEXTRACT functions to achieve what you stated above.

Here's a formula you can use on Cell B3 which not not only lists the status for each item but also leaves the cell blank if there was no entry: =arrayformula(iferror(if(REGEXMATCH(A3:A,"COMPLETE")=True,"COMPLETE",REGEXEXTRACT(A3:A,"\- (.*?)\- M")),""))

Note: Please make sure to remove any values on cells below B3 for this to work.

Here's an example screenshot of the result: Result