New Text and Array Functions for Excel

[spywaregorilla]

Array functions feel like they're just too far from the excel design thinking. A single function that affects nearby cells is hard for me to swallow.

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.

So TEXTSPLIT (which is great, finally), would return an object like ARRAY("I", "SAW","A","CAT") 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.

Or maybe just one single black magic affects-nearby-cells function called "UNPACK"

[sbf501]

I somewhat agree, but I've bent Excel to do things that aren't "excel thinking" for literally decades.

Reshaping is something could have used many times in the past. I used to pull data into PERL first to reshape it but that took a bit of code, then I learned about numpy doing it in one line. But I still have to import back into Excel. The fact that it is in there now is useful to me.

I 1000% agree with your matlab retval suggestion however. I hate how Excel fudges array return values by just blasting a range of cells one time!

My excel knowledge has been somewhat stagnant in the past decade. Have they added an ARRAYFUNC() like in Google Sheets, or do I still need to hit "ctrl-shift-enter" to designate one?

[robocat]

> 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.

I had a go at writing a DLL plugin for Excel that did this years ago. I ended up with a kind of SQL, where each cell has a result set of records. The purpose was to make a functional language for consultants starting with a familiar environment to them. I even integrated a system where you clicked the cell and a pop up would show the data records. It was an ugly proof-of-concept, using strings that just identified each result set, and using custom functions. Excel is beautifully functional, with some nice parallels with SQL, and your data flow/dependencies are naturally visible. Excel is far less scary to most consultants than imperative programming is. I wanted to be able to model the data flows, use sheets for consultants to define custom pure functions for our system, and the final outcome was a reactive data system where data updates could flow (push) into outputs. I failed to get it delivered because I failed to get the COM interfaces working working: I failed to tie together Excel automation as a library engine (Excel COM API), Excel custom functions (plug in DLL), Delphi 7, and my own code.

[deepstack]

>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. you mean kinda like json and jsonb in postgres?

[evandwight]

Don't they have arrays in single cells?

https://www.lifewire.com/excel-single-cell-array-formula-312...

[hbossy]

All they need to do is implement Google Sheets ARRAYFORMULA().

[layer8]

> Scenarios to try: Use “ “ (space) as a delimiter with TEXTBEFORE to extract the first name and TEXTAFTER to extract the last name

I guess this warrants linking to https://shinesolutions.com/2018/01/08/falsehoods-programmers....

[cm2187]

20 years overdue...

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't require to click a button). [edit] actually it was introduced in 2021 ("UNIQUE" function)

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.

Then you can have all sort of finance related function. Validate the checksum for an ISIN, CUSIP, SEDOL, etc.

Excel should also come with the most common holiday list (all the major cities at least).

[WastingMyTime89]

> 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.

You just have to add a column and you are an XLOOKUP and a quick filtering away from the result you want. It’s a fairly common operation.

> Excel should also come with the most common holiday list (all the major cities at least).

It’s the same. You just have to add a table and do a lookup.

[xnx]

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

[auxym]

Agreed. You can do regex via VBA but I really want a built-in cell function for regex search, regex extract, and regex replace.

[guhidalg]

XLOOKUP has some simple simple regex patterns.

[alwillis]

Apple’s Numbers spreadsheet also has regex support FYI.

[badrabbit]

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

[ehsankia]

That would be awesome. With Sheets, I often jump into AppScript to write a quick javascript function to do what I want instead of having to deal with weird syntax. I wish I could do the same in Python.

[russellbeattie]

If you're really a novice, watch Joel Spolsky's talk. I go back to it whenever I end up in Excel for something.

https://youtu.be/0nbkaYsR94c

[xlslimdev]

I created an addin that gives you Python in Excel. Take a look at https://xlslim.com it is easy to use, yet very powerful.

[dasil003]

If you want to lean into the novice feeling you could watch this: https://www.youtube.com/watch?v=x1RVNGDSdw4

[carabiner]

I'm surprised it's 2022 and they haven't embraced a multiline equation editor.

[DavidPeiffer]

Not sure when it became a feature, but since at least 5 years ago you can click and drag the equation edit bar down to reveal multiple lines for editing.

Pair this with Alt+Enter (line break) and adding 4 spaces at the start and you can nest functions in a familiar, albeit manual, way.

[Valgrim]

There is a microsoft garage project that helps you do it that way:

https://www.microsoft.com/en-us/garage/profiles/advanced-for...

[hermitcrab]

Excel is the second best tool for every task!

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.

[dhosek]

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)

[auxym]

Encoding data in cell formatting is questionable practice.

[richardpetersen]

Can’t you achieve that with the new excel lambda function?

[leokennis]

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

[unsupp0rted]

CTRL+F… text not found… what do you mean, I’m looking right at an example of this string… Edit->Find… oh by default it ignores the value of a cell and searches inside the formula that generates that value… change to search by value.

Next day… CTRL+F… text not found … what do you mean, I’m looking right at an example of this string…

[mistermann]

I think it would be nice if they added a feature where you could visually tell what cell you have highlighted. Bigger screens nowadays, I always have to look in the upper left to see what cell I am in, and then find the row and the column on the left and then trace across and down and voila, there is the highlighted cell.

Making it a substantially different color outline or something would be a nice feature. Maybe a 2032 feature.

[waynenilsen]

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

[auxym]

I am not familiar with the R ecosystem, but have you tried PoweredQuery?

I had become my go-to for all sorts for all sorts of data munging in excel.

[McNutty]

For me personally,these new text functions will be great for working with IP address, MAC address, FQDN, and URL

There are already ugly, kludgey ways of doing it (or doing it outside of Excel altogether) but this will be faster and more elegant

[Someone]

I wish they completed the IF variants of statistical functions.

Why do we have AVERAGEIF, but not AVERAGEAIF or GEOMEANIF, MAX and MAXIFS, but not MAXIF, etc?

I can’t find logic in that.

(https://support.microsoft.com/en-us/office/excel-functions-b...)

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.

While at it, these IF functions should use lambdas for specifying the criteria instead of strings such as “>5”

[laumars]

There is a separate IF function

https://support.microsoft.com/en-us/office/if-function-69aed...

[lmg643]

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

[omarhaneef]

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

[armchairhacker]

seriously though, I think a copilot-like ML to improve flash fill is a genuinely amazing idea which could save insane hours, especially with people less familiar with complex formulas/coding