How Do I Sort Alpha Numerically?

[Dazzawm]

I have numbers in the format of CB1, CB2, CB11, CB23 etc etc. When I do a sort it will only sort like below

CB1
CB10
CB11
CB2

rather than

CB1
CB2
CB10 etc etc

How do I get round this please?

 

[healey21]

A suggestion is to extract the numbers into another column (helper) then you can sort on the helper column. Formula used here is:

Sheet7

■	B	C
2	CB1	1
3	CB2	2
4	CB10	10
5	CB11	11

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 {=1*MID(B2,MATCH(TRUE,ISNUMBER(1*MID(B2,ROW($1:$9),1)),0),COUNT(1*MID(B2,ROW($1:$9),1)))} C3 {=1*MID(B3,MATCH(TRUE,ISNUMBER(1*MID(B3,ROW($1:$9),1)),0),COUNT(1*MID(B3,ROW($1:$9),1)))} C4 {=1*MID(B4,MATCH(TRUE,ISNUMBER(1*MID(B4,ROW($1:$9),1)),0),COUNT(1*MID(B4,ROW($1:$9),1)))} C5 {=1*MID(B5,MATCH(TRUE,ISNUMBER(1*MID(B5,ROW($1:$9),1)),0),COUNT(1*MID(B5,ROW($1:$9),1)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Dazzawm]

Is there no easier way?

 

[Dazzawm]

Any more ideas anyone please?

 

[pgc01]

Hi

If you would post more details maybe there could be an easier solution.

For ex. let's say that you say that the alpha part is always 2 characters and that the number of digits that follow it is maximum 4. In that case for a code in A2 you could simply use in B2:

=0+MID(A2,3,4)

 

[Dazzawm]

Ok I will shortly, so the only way would be by using a formula?

 

[pgc01]

Ok I will shortly, so the only way would be by using a formula?

... or vba, which would be a complex solution to a simple problem. You add the auxiliary column, sort and delete the auxiliary column (or hide it) in just seconds.

As far as I know, sorting directly, with some excel sort option, is not possible.

 

[MickG]

Perhaps this:-
Results in column "B"

[COLOR="Navy"]Sub[/COLOR] MG05Apr33
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))


[COLOR="Navy"]For[/COLOR] i = 1 To UBound(Ray)
    [COLOR="Navy"]For[/COLOR] j = i To UBound(Ray)
        [COLOR="Navy"]If[/COLOR] Val(Mid(Ray(j, 1), 3)) < Val(Mid(Ray(i, 1), 3)) [COLOR="Navy"]Then[/COLOR]
            Temp = Ray(i, 1)
            Ray(i, 1) = Ray(j, 1)
            Ray(j, 1) = Temp
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] j
[COLOR="Navy"]Next[/COLOR] i
Range("B1").Resize(UBound(Ray)) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

I tried that Mick but it didn't work. You can see the result below.

Excel 2007
	A	B	C
1	Before Code	After Code	Should Be
2	C1	C1	C1
3	C10	C10	C2
4	C100	C100	C3
5	C101	C2	C4
6	C102	C20	C5
7	C103	C3	C6
8	C104	C30	C7
9	C105	C4	C8
10	C106	C40	C9
11	C107	C5	C10
12	C108	C50	C11
13	C109	C6	C12
14	C11	C60	C13
15	C110	C7	C14
16	C111	C70	C15
17	C112	C8	C16
18	C113	C80	C17
19	C114	C9	C18
20	C115	C90	C19
21	C116	C61	C20
22	C117	C41	C21
23	C118	C11	C22
24	C119	C71	C23
25	C12	C31	C24
26	C120	C51	C25
27	C121	C81	C26
28	C122	C21	C27
29	C13	C101	C28
30	C14	C91	C29
31	C15	C52	C30
32	C16	C72	C31
33	C17	C22	C32
34	C18	C42	C33
35	C19	C32	C34
36	C2	C82	C35
37	C20	C62	C36
38	C21	C12	C37
39	C22	C102	C38
40	C23	C92	C39
41	C24	C23	C40
42	C25	C103	C41
43	C26	C63	C42
44	C27	C33	C43
45	C28	C83	C44
46	C29	C53	C45
47	C3	C43	C46
48	C30	C13	C47
49	C31	C73	C48
50	C32	C93	C49
51	C33	C54	C50
52	C34	C14	C51
53	C35	C104	C52
54	C36	C84	C53
55	C37	C34	C54
56	C38	C24	C55
57	C39	C74	C56
58	C4	C44	C57
59	C40	C64	C58
60	C41	C94	C59
61	C42	C45	C60
62	C43	C25	C61
63	C44	C85	C62
64	C45	C55	C63
65	C46	C75	C64
66	C47	C65	C65
67	C48	C35	C66
68	C49	C105	C67
69	C5	C15	C68
70	C50	C95	C69
71	C51	C66	C70
72	C52	C86	C71
73	C53	C76	C72
74	C54	C16	C73
75	C55	C46	C74
76	C56	C36	C75
77	C57	C106	C76
78	C58	C56	C77
79	C59	C26	C78
80	C6	C96	C79
81	C60	C87	C80
82	C61	C107	C81
83	C62	C17	C82
84	C63	C57	C83
85	C64	C37	C84
86	C65	C47	C85
87	C66	C67	C86
88	C67	C27	C87
89	C68	C77	C88
90	C69	C97	C89
91	C7	C48	C90
92	C70	C108	C91
93	C71	C28	C92
94	C72	C68	C93
95	C73	C38	C94
96	C74	C18	C95
97	C75	C78	C96
98	C76	C58	C97
Sheet3

 

[MickG]

In your initial thread you stated that the data for sorted was like:-CB1, CB2, CB11, CB23 etc etc
Now you've changed it to :- C1, C2, C11, C23 etc etc
If there is only one letter at the start change the "3" to a "2" as below:-

If Val(Mid(Ray(j, 1), 2)) < Val(Mid(Ray(i, 1), 2)) Then