Forum Discussion
How to sort numbers with a space in the one cell
Just to show how different a modern version of Excel can be, the following worksheet formula provides a very different solution strategy
It is a mix of insider beta functionality and a legacy XLM macro 4 function.
Since September 2018 there has been seismic changes in Excel. By 'seismic', I mean it is now possible to write Excel solutions that an average user might not even recognise as being Excel. In the post, have only used worksheet functions, there is no macro other than the old EVALUATE function that takes a text string and evaluates it as if it were a formula.
The Excel 365 changes (later in the year this should also be Office 2021) I refer to are to make array formulas the default without needing CSE. The there is the LET function that allows every alternate parameter to be a name created by the user, which refers to the formula in the following parameter. This helps make sense of deeply nested formulas and those that repeat the use of a calculation fragment.
The final change, that is currently only on release in the beta versions of Excel, is the LAMBDA function. It was this that allowed me to define the name
= CELL.SORT(seq)
which refers to the formula written over several lines (using Alt/Enter to generate a line feed). The LAMBDA function has the names to be used within the formula as its initial parameters and the function to evaluate as its final parameter. The thing that makes the function so revolutionary is that the LAMBDA is followed by the actual parameters to be used in the formula, held within parentheses. These are then substituted one for one in place of the dummy parameters which means that the same defined function can be used to refer to many different areas of the worksheet.
What makes the whole thing so startling to anyone schooled to accept traditional spreadsheet practice is that the resulting formulae read more like a snippet of code than a conventional formula.
- My dear friend I'm well versed with all you have written here,, my concern was,,, being a answerer ,, it's the prime responsibilities that one must show the correct way to create UDFs,,, VBA stuffs,,, and their implementations,, if I'm not wrong hardly 1% users can handle this new arrival feature,,, . So please show the way you have created this function !!
I am not quite sure what you hope to see from me.
Having tackled the problem as a challenge, I posted the resulting method as an antidote for the usual 'tips and tricks' genre, which would lead the reader to believing that single-cell filled formulas with relative referencing is the only way to develop spreadsheets. I left posting until the OP had been given methods that would work with any version of Excel to avoid pressurising them into updating their software.
In case it is of interest to anyone, I have attached a copy of my workbook. It is a macro-enabled workbook both because it contains the XLM4 function EVALUATE and it contains a macro that creates defined names and uploads formulae prepared on the worksheet. Besides the main formula that relies on LAMBDA functions and LET, it also implements formulas that just rely on FILTER and SORT. I could revert the functionality further, but my intention was to illustrate a possible future for Excel, not its past.
EVALUATE() is available in any version of Excel starting from Excel 4.0.
- That I know ,,, my expectation is that the answerer must show the correct way to create & implementation of UDFs ,, Macro & features like LAMBDA,,, how may users are familiar with new arrivals !!
Sorry, I didn't catch. Code for both UDF (cell.value and eval) is presented. The rest are native functions like SORT(), LAMBDA(), SUBSTITUTE(), LET()
