Search code examples
regexgoogle-sheetsgoogle-sheets-formulaextract

Extract Required Data from the Cell Through Formula

I have been using below formula which extract the desired result from the cell but now i am trying to get more data from the cell and i have tried at my end to get further values from the cell but its not working.

I have attached a Google Sheet where data and desired result is available and as well my fomrula.

any help will be appreciated.

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

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

Solution

  • Your solution is a similar formula

    =ArrayFormula(TRIM(REGEXREPLACE(A3:A,"\.\.\.(.*)|\*\*\*","")))

    You use TRIM only to reject empty spaces from beginning or end of result.

    The formula keeps only everything between *** and ...