Forum Discussion

JRoy-55's avatar
JRoy-55
Copper Contributor
Aug 26, 2019

SPECIAL NUMBER SORTING

I am trying to sort a spreadsheet with special numbers. it puts 129.01.00A ahead of 13.01.00, 71.01.00 etc. I want it to sort by the number ahead of the first decimal, then by the second, etc. e.g. 13.01.00 then 71.01.00 then 129.01.00A I have tried formatting by number, by general, by accounting. Any help appreciated.
Sorting

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 26, 2019

    JRoy-55 

    One variant is with VBA function. If with formulas, you may add helper columns for each level like

    Formulas could be

    Leve l:
=--LEFT(A2,SEARCH(".",A2)-1)

Level 2:
=--MID(A2,SEARCH(".",A2)+1,SEARCH(".",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)-1)

Level 3:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))),1))

    After that select all 4 columns and Data->Sort, add 3 levels of sorting. Helper columns could be hided after that.

Resources