New Text and Array Functions for Excel

Array functions feel like they&#x27;re just too far from the excel design thinking. A single function that affects nearby cells is hard for me to swallow.<p>I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell.<p>So TEXTSPLIT (which is great, finally), would return an object like ARRAY(&quot;I&quot;, &quot;SAW&quot;,&quot;A&quot;,&quot;CAT&quot;) and if you wanted to unpack it you could drag a formula that was something like =$A$1?0, =$A$1?1, =$A$1?2, etc.<p>Or maybe just one single black magic affects-nearby-cells function called &quot;UNPACK&quot;

&gt; Scenarios to try: Use “ “ (space) as a delimiter with TEXTBEFORE to extract the first name and TEXTAFTER to extract the last name<p>I guess this warrants linking to <a href="https:&#x2F;&#x2F;shinesolutions.com&#x2F;2018&#x2F;01&#x2F;08&#x2F;falsehoods-programmers-believe-about-names-with-examples&#x2F;" rel="nofollow">https:&#x2F;&#x2F;shinesolutions.com&#x2F;2018&#x2F;01&#x2F;08&#x2F;falsehoods-programmers...</a>.

20 years overdue...<p>Another one that is massively overdue: take multiple arrays as arguments and return the distinct values, sorted (kind of like the remove duplicate button, but that doesn&#x27;t require to click a button). [edit] actually it was introduced in 2021 (&quot;UNIQUE&quot; function)<p>Also take multiple arrays and returns the values that are in common (like an inner join). Use case: you want to align two time series by creating a 3rd time series made of the dates common to both original time series.<p>Then you can have all sort of finance related function. Validate the checksum for an ISIN, CUSIP, SEDOL, etc.<p>Excel should also come with the most common holiday list (all the major cities at least).

All these new text functions and still no support for regex? Google Sheets has had regex support for years.

Excel makes me feel like such a novice. I often just write a python script instead out of frustration. I don&#x27;t think little of people that write complex formulas and macros with it. I am still waiting for &quot;python in excel&quot;.

I&#x27;m surprised it&#x27;s 2022 and they haven&#x27;t embraced a multiline equation editor.

Excel is the second best tool for every task!<p>If you want to do lots of reshaping data and performing operations on tables of data, you are better using ETL tools (Extract Transform Load) that were designed for this task. For example: Easy Data Transform, Tableau Prep or Alteryx.

The functionality I want is a count-by-format function. I had to write my own in VBA and of course that means every time I open that sheet I have to approve its use of macros (and it also doesn’t always catch format changes that impact the calculation)

Next challenge: make the find&#x2F;replace dialog better than the confusing tabbed mess it is today. And make it non-modal for simple search&#x2F;replace.

I am very surprised they have not yet embraced the dplyr&#x2F;tidyr-style melt&#x2F;cast gather&#x2F;spread pivot&#x2F;unpivot functions for arrays

For me personally,these new text functions will be great for working with IP address, MAC address, FQDN, and URL<p>There are already ugly, kludgey ways of doing it (or doing it outside of Excel altogether) but this will be faster and more elegant

I wish they completed the <i>IF</i> variants of statistical functions.<p>Why do we have <i>AVERAGEIF</i>, but not <i>AVERAGEAIF</i> or <i>GEOMEANIF</i>, <i>MAX</i> and <i>MAXIFS</i>, but not <i>MAXIF</i>, etc?<p>I can’t find logic in that.<p>(<a href="https:&#x2F;&#x2F;support.microsoft.com&#x2F;en-us&#x2F;office&#x2F;excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb" rel="nofollow">https:&#x2F;&#x2F;support.microsoft.com&#x2F;en-us&#x2F;office&#x2F;excel-functions-b...</a>)<p>Ideally, the “IF” would be a separate function that filters out cells, so that it could be used with VBA functions taking multiple cells, too.<p>While at it, these <i>IF</i> functions should use lambdas for specifying the criteria instead of strings such as “&gt;5”

love to see this stuff, reacting to users in the world&#x27;s #1 accidental entry point to programming. I wish they did this 20 years ago when i was neck deep in Excel daily!

It’s 2022 and how come they don’t have GPT4 and some stable diffusion built in?