Forum Discussion

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

Silver Contributor

Jul 31, 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

Diamond Contributor

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),
)