Announcing New Text and Array Functions

Is there any chance that a full conventional Regex will be introduced into Word 365?

Keith_A_Lewis 

Agreed - They are in C / C++ but the fact remains that in my scenario, when the Regex is called from new excel function it does not work but when called from Python it works flawlessly as shown above. As I have not delved deeper into the anatomy of regex but rather focussed on their usage, it is possible that I do not know certain things. I am actually not a programmer or developer but rather explorer of Regular expressions in their usage in commercial scenario (finance and others).

jignesh0yt2k You seem to be under the misconception that the regular expressions are written in Python. They are written in C or C++ and get called from Python. They can be called from any language.

JakeArmstrong 

I think this array of arrays is a common problem shared by many others in previous posts. Python handles it aptly but excel finds it challenging except with complicated maze of nested LET and other new excel 365 native functions recently introduced, as demonstrated above. I am reluctant to go on that path with a risk of being lost without solution but rather am interested in finding simple solution may it be with excel native functions or without them using workarounds like using Python UDF with xlwings as bridge.

Therefore, to keep it simple, speedy and understandable to me, I have moved on the hybrid path instead of an only-strictly-excel solution.

As for Regular Expressions, understandably as these excel functions been just introduced, they have yet to mature to Python's  level (Not sure - maybe I am wrong but as demonstrated above, these work smoothly with Python as backend rather than without it in, at least in my case).

JakeArmstrong 

Thank you for your efforts.

Here is the shared file link - File has been uploaded to onedrive: amfi_data.txt

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)

jignesh0yt2k, unfortunately I'm not able to download and import your data. but it seems to me that removing the "^" is changing your regex pattern. "^" is supported in the new REGEX functions, so perhaps it's worth looking into that. If you're getting #N/A! from the function, it means that it's not finding any matches. I would also recommend using [return_mode] = 0, which will only return the first result. Since you're using a structured column reference for your text argument, I assume that input has multiple values. This will cause you to run into the 'arrays of arrays' issue if you get multiple matches returned for multiple entries of text.

Please let me know if any of this sorts out your issues!

Hello SergeiBaklan 

Appreciate your response but I also want to achieve case insensitivity like for example FOF, fOf, FOf, etc and any further permutations / combinations of all these words (FOF and others above) should be covered in the condition for which I think (not sure) regular expressions is the answer. Not sure about support for case insensitivity in power query - Haven't delved deeper in to that part.

Actually what I have covered above is just an example and my actual function in excel with regular expressions span two columns in excel: 
For the first column the pattern is:  (?i)^((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$ and for 2nd column the pattern is: (?i)^((?=.*direct)|(?=.*growth)|(?=.*gold)|(?=.*silver))(^((?!(regular|idcw|dividend|hybrid|balanced advantage|index|nifty)).)*$) (as you can see I want to lookahead positively and negatively at the same time in the second pattern) and I am combining both patterns using python UDF nested with excel IF and CONCAT functions like this:
=IF((REGEXFINDM(PQ___Python_Combo___2021[Scheme Type],CONCAT(AB4))<>"")*(REGEXFINDM(PQ___Python_Combo___2021[Scheme Name],CONCAT(AB3))<>"")=1,PQ___Python_Combo___2021[Net Asset Value],0) Where AB3 and AB4 cells contain above patterns  and if the multiplication of combined conditions = 1, then function returns NAV else it returns 0

Reason behind using CONCAT is my pattern text sometimes hit 255 characters limit imposed by excel.
Hope I am clear in explaining my use case.

jignesh0yt2k , just as a comment, you may filter directly in Power Query

    ...,
    exclude = {"FoF", "Solution Oriented", "Hybrid", "Equity"},
    AddFlag = Table.AddColumn(
        CleanName,
        "Flag",
        each List.AnyTrue(
                List.Transform( exclude, (q) => Text.Contains( [Scheme Type], q ))
             )
    ),
    FilterByFlag = Table.SelectRows(AddFlag, each ([Flag] = false)),
    RemoveFlag = Table.RemoveColumns(FilterByFlag,{"Flag"}),
    ...

What to exclude could be in separate table.

JakeArmstrong 

Here is the link to the data which is a web page containing delimited text file (";" as delimiter). I converted that to excel using power query.

Link to text file:

https://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?frmdt=21-May-2024

I copied and pasted text to excel and converted to a table named Table25.

Below is the text of my power query:

let
Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Scheme Code", type text}, {"Scheme Name", type text}, {"ISIN Div Payout/ISIN Growth", type text}, {"ISIN Div Reinvestment", type text}, {"Net Asset Value", type number}, {"Repurchase Price", type text}, {"Sale Price", type text}, {"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Scheme Type", each if [Scheme Code] is null then [Scheme Code] else if (Text.Contains([Scheme Code],"Open Ended") or Text.Contains([Scheme Code],"Close Ended")) then [Scheme Code] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Scheme Type"}),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Scheme Code", "Scheme Type", "Scheme Name", "ISIN Div Payout/ISIN Growth", "ISIN Div Reinvestment", "Net Asset Value", "Repurchase Price", "Sale Price", "Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Scheme Code"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each ([Scheme Name] <> null))
in
#"Filtered Rows"

As an additional information, after data is split into columns with above PQ, I tried the new function on the column named "Scheme Type"

Repeating my function again here:
=REGEXEXTRACT(PQ___Python_Combo___2021[Scheme Type],AJ6,1,1) [AJ6 contains the pattern: ((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$]

Pattern used for excel function does not seem to cover "^" meaning beginning of the string in regular expression. So I had to delete "^", else the function produced error.

The comparative Python pattern is:
(?i)^((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$ - where (?i) is for ignoring case sensitivity and ^ for start of the string. This pattern correctly returns data using python library re and that UDF was called via xlwings. Other details about that UDF can be found in my above responses.

Seems, excel is yet to support lookaheads / lookbehinds - Positive and Negative - (?=,  ?!,  ?<=,  ?<!, etc.) or am I missing something? 

I have posted a workbook demonstrating the use of REGEEXTRACT to generate an array of capture groups which it then converted to a thunk to hide the nested array property of the results from BYROW.  The post is at or towards the end of

Re: A generalised Lambda helper function that return arrays of arrays using bisection. - Microsoft Community Hub