Forum Discussion
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...
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.