Forum Discussion

Chris_Mercer's avatar
Chris_Mercer
Copper Contributor
Jul 26, 2024

Excel - Sorting hierarchical numbers issue

Hi - I'm looking for some help with something I thought would be quick and easy but is proving not to be the case!   I'm trying to sort a data set of hierarchical numbers but am having issues with ...
Excel
Formulas and Functions
Macros and VBA
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 01, 2024

Chris_Mercer 

For a 365 user, LET variables provide a good alternative to helper ranges

= LET(
    level1, TEXTBEFORE(sectionNumbers, "."),
    level2, VALUE(TEXTAFTER(sectionNumbers, ".")),
    SORTBY(contents, level1,1,level2,1)
  )

  

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 01, 2024

    PeterBartholomew1 

    It doesn't sort correctly "8" (numbers without dots).

    = LET(
    level1, --IFNA(TEXTBEFORE(sectionNumbers, "."), sectionNumbers),
    level2, --IFNA(TEXTAFTER(sectionNumbers, "."),0),
    SORTBY(contents, level1,,level2,)
  )

    Or, with 365 Beta

    =SORTBY(
  contents,
  --REGEXEXTRACT(sectionNumbers, "^\d*", 1),,
  --IFNA(REGEXEXTRACT(sectionNumbers, "(?<=\.)\d*", 1),0),
)
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Aug 01, 2024

      SergeiBaklan 

      Well spotted as far as the single number is concerned.

      The regex is nice though I lack fluency when it comes to group constructs such as the positive lookbehind

      (?<=\.)\d*

      I guess bridging the gap between natural language and the cryptic regex notation is something to which AI is well suited.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 01, 2024

        PeterBartholomew1 

        I don't think AI could help if only

        - the case is quite simple, and/or

        - you have good enough knowledge of RegEx

         

        First, AI answers are not reliable. It could easy recommend Word or Google Sheet settings to apply in Excel, etc.

        Second, RegEx behaviour depends on flavour. What works in Perl or JavaScript or something else could not work in Excel.

Resources