Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New Text and Array Functions for Excel

techcommunity.microsoft.com

Post reply on HN
Print view View original

31–40 of 55 posts

Re: New Text and Array Functions for Excel

#32

Post by dhosek »

auxym wrote: post #19
dhosek wrote: post #13

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)

Encoding data in cell formatting is questionable practice.

Why? Because the tools don’t provide nice ways to interact with it? I use colors and other formatting as ways of providing metadata to cells that I want to be able to move around in the spreadsheet.

Re: New Text and Array Functions for Excel

#33

Post by layer8 »

DavidPeiffer wrote: post #24
carabiner wrote: post #3

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

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.

The resizable formula bar was introduced in Excel 2007: http://projectwoman.com/tag/formula-bar-resizing

Re: New Text and Array Functions for Excel

#34

Post by thfuran »

ehsankia wrote: post #30
_8j50 wrote: post #29

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

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.

Doesn't the libre office equivalent do python?

Re: New Text and Array Functions for Excel

#35

Post by armchairhacker »

evandwight wrote: post #12
armchairhacker wrote: post #10

Earlier quoted context omitted.

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

Someone just created that! https://excelformulabot.com/ Apparently Microsoft is creating a plugin. (Not affiliated)

Not quite what I meant. Basically you fill in some cells, and then expand the region and Excel will autofill. For example if you have

    Full Name        | Nickname
    Smith, John      | Johnny
    Ayers, Danielle  | Dani
    McDougle, Jack   | Jack
    Jason, Charles   |
    James, Trent     |
    Myers, Jessica   |
    David, Katherine |
And if you select the first 4 entries on the right and drag down, it will fill in the last 4.

This is known as flash fill and already implemented: https://support.microsoft.com/en-us/office/using-flash-fill-.... Except AFAIK it still uses hard-coded pattern recognition. This is exactly the type of thing ML is good for!

Re: New Text and Array Functions for Excel

#36

Post by ehsankia »

thfuran wrote: post #34
ehsankia wrote: post #30

Earlier quoted context omitted.

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.

Doesn't the libre office equivalent do python?

Ah, very interesting. Though it seems they only have the document control API. What I like about the Sheets integration is that you can write normal formulas you can use inside cells. like

    function REVERSE_STRING(input) {
      return input.split("").reverse().join("");
    }
and then call that inside a cell formula.

Re: New Text and Array Functions for Excel

#38

Post by _8j50 »

ehsankia wrote: post #30
_8j50 wrote: post #29

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

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.

FYI, I wasn't wishing, this is planned by MS.

Re: New Text and Array Functions for Excel

#39

Post by unsupp0rted »

leokennis wrote: post #7

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.

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…

Re: New Text and Array Functions for Excel

#40

Post by nl »

armchairhacker wrote: post #10
omarhaneef wrote: post #5

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

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

Google Sheets has this and it works really well.

I had an idea Excel had something as well but maybe not?

Post reply on HN