Excel - Sorting hierarchical numbers issue

Question

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

hansvogelaar · Answer

Chris_Mercer&nbsp;
Let's say your hierarchical numbers are in B2 and down.
Change the formula in C2 to
&nbsp;
=LET(n, C2, p, FIND(".", n), IFERROR(1000*LEFT(n, p-1)+MID(n, p+1, 10), 1000*n))
&nbsp;
Fill down.

chris_mercer · Answer

HansVogelaar&nbsp;&nbsp;Hi - thanks for replying, I appreciate it. I've been on vacation so just picking it up now.&nbsp;I changed the data format to text in column B and used the formula in Column C as so and it worked-&nbsp;Many thanks for your help!&nbsp;

peterbartholomew1 · Answer

Chris_Mercer&nbsp;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)
  )&nbsp;&nbsp;

sergeibaklan · Answer

PeterBartholomew1&nbsp;
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, "(?&lt;=\.)\d*", 1),0),
)

peterbartholomew1 · Answer

SergeiBaklan&nbsp;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(?&lt;=\.)\d*I guess bridging the gap between natural language and the cryptic regex notation is something to which AI is well suited.&nbsp;

Forum Discussion

Excel - Sorting hierarchical numbers issue

Share

Resources