Sorting

[Carol_Caesar]

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]

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

[#3]

@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.