excelexcel-formuladynamicoffice365

Excel - Sorting textual versions using dynamic arrays

I need to sort versions of software, and like you can see in this picture, the source is in 6 colomns (it can change), stored in a dynamic array.

I need only one colomn with the higher versions in numbers, more or less like i tried to do in the two last columns. Is two different try to get the output, without succes.

enter image description here

Here the formula :

=LET(
    _a; ESTNUM(CHERCHE("7.";A1#));
    _b; ESTNUM(CHERCHE("8.";A1#));
    _c;SI(_a+_b;A1#;"");
    _d;BYROW(_c;LAMBDA(x;CHOISIRLIGNES(TRIER(TRANSPOSE(x);;-1);1)));
    _e;BYROW(_c;LAMBDA(x;CHOISIRCOLS(FILTRE(x;x<>"";"");1)));
    ASSEMB.H(_d;_e)
)

In english :

=LET(
    _a, ISNUMBER(SEARCH("7.",A1#)),
    _b, ISNUMBER(SEARCH("8.",A1#)),
    _c, IF(_a+_b,A1#,""),
    _d, BYROW(_c,LAMBDA(x,CHOOSEROWS(SORT(TRANSPOSE(x),,-1),1))),
    _e, BYROW(_c,LAMBDA(x,CHOOSESCOL(FILTER(x,x<>"",""),1))),
    HSTACK(_d,_e)
)

here _d, i my best result I think, but not perfect. Have you any idea to reach my goal ?

I'm using O365 and dynamic arrays

Solution

  • This approach involves using regex repeatedly: screenshot illustrating effect of suggested formula

    =REGEXREPLACE(LET(dat,LET(dat,
REGEXREPLACE(A1#,"(?<=\.)(\d)(?=\.)","0$1"),
IF(NOT(REGEXTEST(dat,"\.")),"",dat)),
BYROW(dat,LAMBDA(r,@SORT(r,,-1,-1)))),
"\b0(\d)\b","$1")

    or

    =REGEX.REMPLACER(LET(dat;LET(dat;
REGEX.REMPLACER(A1#;"(?<=\.)(\d)(?=\.)";"0$1");
SI(NON(REGEX.TEST(dat;"\."));"";dat));
BYROW(dat;LAMBDA(r;@TRIER(r;;-1;-1))));
"\b0(\d)\b";"$1")

    The functionality is used