Sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-1712138%22%20slang%3D%22en-US%22%3ESorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1712138%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20sort%20samples%20containing%20a%20letter%20followed%20by%20a%20number%20from%201-10.%20When%20they're%20sorted%2C%20they're%20sorted%20alphabetically%20but%20the%20numbers%20sort%20as%201%2C%2010%2C%202%2C%203%20etc.%20Does%20anyone%20know%20how%20to%20sort%20so%20that%20the%2010%20comes%20after%209%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1712138%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1712214%22%20slang%3D%22de-DE%22%3ESubject%3A%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1712214%22%20slang%3D%22de-DE%22%3EJust%20to%20untested%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3E%20German%20Form%20Exampel%20%3DWENN(AND(LINKS(A2%3B1)%26gt%3B%3D%22A%22%3B%20LINKS(A2%3B1)%20%26lt%3B%3D%22Z%22)%3B%22A%22%3B%22Z%22)%20%3CBR%20%2F%3E%20English%20formula%20tranlation%20from%20german%3A%20%3DIF(AND(LEFT(A2%2C1)%26gt%3B%3D%22A%22%2CLEFT(A2%2C1)%20%26lt%3B%3D%22Z%22)%2C%22A%22%2C%22Z%22)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Sort%20in%20pivot%20by%20number%20with%20letter%20%3CBR%20%2F%3E%20German%3A%20s%3DTEXT(LINKS(B2%3B%20NUMBER%20(LINKS(B2%3B%20COLUMN(1%3A1))*1))%3B%22%20000%22)%26amp%3BRECHTS(B2%3B%20LENGTH%20(B2)%20NUMBER%20(LINKS(B2%3B%20SPALTE(1%3A1))*1))%20English%20%3CBR%20%2F%3E%20formula%20tranlation%20from%20german%3A%20'%3DTEXT(LEFT(B2%2CCOUNT(LEFT(B2%2CCOLUMN(1%3A1))*1))%2C'000')%26amp%3BRIGHT(B2%2CLEN(B2)-COUNT(LEFT(B2%2C%20COLUMN(1%3A1))*1))%20%3CBR%20%2F%3E%20Finish%20with%20Ctrl%20%2B%20Shift%20%2B%20Enter%20Thank%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20you%20for%20letting%20me%20suggest%20%2F%20solution%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Visitor

Hello

 

I am trying to sort samples containing a letter followed by a number from 1-10. When they're sorted, they're sorted alphabetically but the numbers sort as 1, 10, 2, 3 etc. Does anyone know how to sort so that the 10 comes after 9?

 

Thanks 

2 Replies
Just an untested
German Formular Exampel =WENN(UND(LINKS(A2;1)>="A";LINKS(A2;1)<="Z");"A";"Z")
English formula tranlation from german: =IF(AND(LEFT(A2,1)>="A",LEFT(A2,1)<="Z"),"A","Z")

Sort in pivot by number with letter
German: {=TEXT(LINKS(B2;ANZAHL(LINKS(B2;SPALTE(1:1))*1));"000")&RECHTS(B2;LÄNGE(B2)-ANZAHL(LINKS(B2; SPALTE(1:1))*1)) }
English formula tranlation from german: {=TEXT(LEFT(B2,COUNT(LEFT(B2,COLUMN(1:1))*1)),"000")&RIGHT(B2,LEN(B2)-COUNT(LEFT(B2, COLUMN(1:1))*1)) }
Finish with Ctrl + Shift + Enter


Thank you for letting me suggest an approach / solution / help.

Nikolino
I know I don't know anything (Socrates

@Carol_Caesar This could be easily achieved by "Get&Transform Date" a.k.a. PowerQuery.

 

1. Convert your list of sample codes to a structured table

2. Query it

3. Split the code (by non-digit to digit).

4. Change the type of the number column to "123" (=whole number)

5. Sort first by letter then by number

6. Merge the two columns and load the end result back to Excel.

 

The attached workbook contains a working example.