Microsoft
Thank you for your efforts.
Here is the shared file link - File has been uploaded to onedrive: https://1drv.ms/t/c/49736323b50e9a8c/EZmyHnCS_IpGmcU9giOkEPcBBFwENZmQuaaRScX5H70osQ?e=WwOhr9
After including "^" in the beginning, the result is as under (given at the end). I think there are some incompatibilities between how Python and excel recognizes the regular expressions (or may be other reasons not known to me). As stated above, my actual use case is little complex and involves multiple regular expressions spanning 2 columns (finally combined using boolean 1 or 0 by multiplication). Patterns involve both lookaheads (negative and positive) expressions in the same pattern.
My final function in excel (using python UDF nested with excel's native functions) that returned correctly, the scheme name and NAV data with desired filtering using Regex is:
=SORT(DROP(UNIQUE(IF((REGEXFINDM(PQ___Python_Combo___2021[Scheme Type],CONCAT(AB4))<>"")*(REGEXFINDM(PQ___Python_Combo___2021[Scheme Name],CONCAT(AB3))<>"")=1,SPLIT_TEXT(PQ___Python_Combo___2021[Scheme Name]&";"&VALUE(PQ___Python_Combo___2021[Net Asset Value]),";"),0)),1),1,1) [Patterns used:
(?i)^((?=.*direct)|(?=.*growth)|(?=.*gold)|(?=.*silver))(^((?!(regular|idcw|dividend|hybrid|balanced advantage|index|nifty)).)*$) (for filtering Scheme Name Column) [Cell AB3 in the function]
and
(?i)^((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$ (for filtering Scheme Type Column) [Cell AB4 in the function]
Excel's new REGEXEXTRACT function (Pattern modified and (?i) deleted (^ included) to make it compatible with excel - Example is only for Cell AB4 pattern) :
^((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$ [Pattern]
Output:
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
Open Ended Schemes ( Money Market )
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Update: Return mode 0 also did not change the above output: =REGEXEXTRACT(PQ___Python_Combo___2021[Scheme Type],AJ6,0,1)
