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 the 8.1 & 8.10 values (etc.), whereby the sort is treating them both as 8.1 - 

 

Copying the data from another piece of software into a table it looks like this -

 

After sorting (by smallest to largest) it treats both the same and returns -

 

I looked on line and found a post suggesting using a VBA function which treats the string as text to sort - 

 

Unfortunately this still returned the same value for the 8.1 & 8.10 items -

 

Any help would be greatly appreciated.

 

Thanks

 

 

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.

         

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 26, 2024

    Chris_Mercer 

    Let's say your hierarchical numbers are in B2 and down.

    Change the formula in C2 to

     

    =LET(n, C2, p, FIND(".", n), IFERROR(1000*LEFT(n, p-1)+MID(n, p+1, 10), 1000*n))

     

    Fill down.

    • Chris_Mercer's avatar
      Chris_Mercer
      Copper Contributor
      Aug 01, 2024

      HansVogelaar 

       

      Hi - thanks for replying, I appreciate it. I've been on vacation so just picking it up now.

       

      I changed the data format to text in column B and used the formula in Column C as so and it worked-

       

      Many thanks for your help! 

Resources