MicrosoftThank you JoeMcDaid
Tried the beta - Only REGEXEXTRACT. Am I doing something wrong, since output is not as expected? Seemingly no full support for groups, lookahead / lookbehind in excel yet, unlike Python? Did not get desired output as I could with Python's re library. Example given below.
Use case description: I am trying to extract list of mutual fund schemes from the Power Query converted text to excel output, which do not contain words equity / hybrid / solution oriented / FOF / elss (case insensitive)
Comparison:
Python: pattern: (?i)^((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$
Python UDF called using xlwings add-in (Combined with excel's FILTER to avoid getting empty rows): =FILTER(REGEXFINDM(PQ___Python_Combo___2021[Scheme Type],AB6),REGEXFINDM(PQ___Python_Combo___2021[Scheme Type],AB6)<>"") [AB6 contains above pattern]
Test output:
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Open Ended Schemes ( Debt Scheme - Overnight Fund )
Excel's version:
Pattern (Modified slightly because case insensitivity is built into excel function): ((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$ - Did not include ^ at the beginning - It results in error #N/A
Function used:
=REGEXEXTRACT(PQ___Python_Combo___2021[Scheme Type],AJ6,1,1) [AJ6 contains above pattern]
Sample Output (varies significantly):
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
quity Scheme - Multi Cap Fund )
quity Scheme - Multi Cap Fund )
quity Scheme - Multi Cap Fund )
quity Scheme - Multi Cap Fund )
