CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formula
HSHO
HSHO

Reputation: 537

How to Pull the Required Text from the Data

I have been trying to extract the data which is pending. The data which has ? or this (Ordered but Pending). Then formula should get that whole text from the data.

I have attached a sheet Link for reference. any help will be appreciated.

=IFERROR(REGEXEXTRACT(A2,"^\*\*\*\ PEELS - (.*?\?) +- "))

Upvotes: 0

Views: 45

Answers (1)

The fourth bird
The fourth bird

Reputation: 163517

For you example data using REGEXREPLACE, you can first capture in group 1 matching until the last occurrence of - and then optionally match until =>

Then in group 2 capture until the last occurrence of Ordered bu Pending).

As there are also empty results in the sheet, you can make the whole pattern optional, and at the end match the whole line, so that if there are no matches for the groups the whole line will be matched.

In the replacement use 2 capture groups.

^(?:\*\*\*\ PEELS - (.*\?)(?:.*-\d+\s*(?:.*?DONE=>\s*)?(.+\(Ordered but Pending\)))?)?.*

Regex demo

For example

=REGEXREPLACE(A2, "^(?:\*\*\*\ PEELS - (.*\?)(?:.*-\d+\s*(?:.*?DONE=>\s*)?(.+Ordered but Pending\)))?)?.*", "$1 $2")

enter image description here

Upvotes: 1

Related Questions