Admin
1
I have been Extracting a data from the cell where i need more result but my formula is extracting some data but not the whole as i need.
I have attached a sheet below will appreciate if i could get a help.
My formulas.
=ArrayFormula(TRIM(REGEXREPLACE(A3:A,"\.\.\.(.*)|\*\*\*","")))
=ArrayFormula(IFERROR(TRIM(REGEXEXTRACT(A3:A, "DONE=>\s*.+\b"))))
https://docs.google.com/spreadsheets/d/1MKC1OWIj64v_mmuNM6mLFY9wMgLwl2mUxm6KnsM5arE/edit#gid=0
The regexps you can use are
=ArrayFormula(TRIM(REGEXREPLACE(A3:A,"(\*{3}.*?)(?:\s*\.{3}DONE=>.*)?(\*{3})$","$1 $2")))
=ArrayFormula(IFERROR(TRIM(REGEXEXTRACT(REGEXREPLACE(A3:A, "^([^-]*-)[^-]+-", "$1"), ".*DONE=>.*"))))
See the first regex demo and the second regex demo. The third one - .*DONE=>.* - simply returns all the strings that contain DONE=> in them.
Details:
(\*{3}.*?) - Group 1 ($1): three * chars and then any zero or more chars other than line break chars, as few as possible(?:\s*\.{3}DONE=>.*)? - an optional string of zero or more whitespaces, ***DONE=> and then the rest of the string(\*{3}) - Group 2 ($2): *** string$ - end of string.The ^([^-]*-)[^-]+- matches
^ - start of string([^-]*-) - Group 1 ($1): any zero or more chars other than - and then a -[^-]+- - one or more chars other than - and then a - char.You say "Thank you but it includes ? value in last"
Completely new formula for your needs
We put front part and last part together with &
=ArrayFormula(IF(REGEXMATCH(A2:A,"MUKHML"),TRIM((REGEXEXTRACT(A2:A,"^[^-]*")®EXREPLACE(A2:A,".*\?|.* COMPLEXIES",""))),""))
Use this new formula like from Wiktor
=ArrayFormula(IF(REGEXMATCH(A2:a,"MUKHML"),REGEXREPLACE(A2:a,"^([^-]*-)[^-]+-","$1"),""))
