How Do I Sort Alpha Numerically?

Dazzawm · Apr 4, 2014

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?

Dazzawm · Apr 5, 2014

Sorry my mistake, its not for me its for a colleague. I think there may be a mixture of 1 letter and 2 letters, can the code be made for both possibilities please?

MickG · Apr 5, 2014

Try this:-

Code:

[COLOR=Navy]Sub[/COLOR] MG05Apr53
[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] jj [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] ii [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
Ray = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] i = 1 To UBound(Ray)
    [COLOR=Navy]For[/COLOR] j = i To UBound(Ray)
        jj = IIf(IsNumeric(Mid(Ray(j, 1), 2, 1)), 2, 3)
        ii = IIf(IsNumeric(Mid(Ray(i, 1), 2, 1)), 2, 3)
        [COLOR=Navy]If[/COLOR] Val(Mid(Ray(j, 1), jj)) < Val(Mid(Ray(i, 1), ii)) [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 · Apr 5, 2014

With this the code does work but not quite as needed. Please see below. Its putting all the numbers in order but really it needs to put the letters then numbers like the reult in the 3rd column.

Excel 2007

A

B

C

1

Before Code

After Code

Should Be

2

C1

3

C10

CB1

C2

4

C11

C2

C3

5

C12

CB2

C4

6

C13

C3

C5

7

C14

CB3

C6

8

C15

C4

C7

9

C16

CB4

C8

10

C17

C5

C9

11

C18

CB5

C10

12

C19

C6

C11

13

C2

CB6

C12

14

C3

C7

C13

15

C4

CB7

C14

16

C5

C8

C15

17

C6

CB8

C16

18

C7

C9

C17

19

C8

CB9

C18

20

C9

C10

C19

21

CB1

CB10

CB1

22

CB10

C11

CB2

23

CB11

CB3

24

CB12

C12

CB4

25

CB13

CB12

CB5

26

CB14

C13

CB6

27

CB15

CB13

CB7

28

CB16

C14

CB8

29

CB17

CB14

CB9

30

CB18

C15

CB10

31

CB19

CB15

CB11

32

CB2

C16

CB12

33

CB20

CB16

CB13

34

CB3

C17

CB14

35

CB4

CB17

CB15

36

CB5

C18

CB16

37

CB6

CB18

CB17

38

CB7

C19

CB18

39

CB8

CB19

40

CB9

CB20

Sheet3

Dazzawm · Apr 6, 2014

Also Mick I've been informed that there will be other columns that will need sorting with the data, so maybe rather than putting the result in column b can the code sort column a and the respective data alongside also. Just like if you selected the entire sheet and used the normal sort function in Excel. Thanks.

MickG · Apr 6, 2014

Try this:-
The number of columns sorted, relates to the length of columns with data in row 1

Code:

[COLOR="Navy"]Sub[/COLOR] MG06Apr30
[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] jj      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ii      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, Lst)


[COLOR="Navy"]For[/COLOR] i = 1 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] j = i To UBound(Ray, 1)
        jj = IIf(IsNumeric(Mid(Ray(j, 1), 2, 1)), 2, 3)
        ii = IIf(IsNumeric(Mid(Ray(i, 1), 2, 1)), 2, 3)
         [COLOR="Navy"]If[/COLOR] Val(Mid(Ray(j, 1), jj)) < Val(Mid(Ray(i, 1), ii)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] Ac = 1 To Lst
                Temp = Ray(i, Ac)
                Ray(i, Ac) = Ray(j, Ac)
                Ray(j, Ac) = Temp
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] j
[COLOR="Navy"]Next[/COLOR] i
Range("A1").Resize(UBound(Ray), Lst) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Dazzawm · Apr 6, 2014

Thanks Mick I will try at work tomorrow.

GTO · Apr 6, 2014

I just caught the part about sorting adjacent columns. This would just handle the column with the funky alphanumeric strings.

Option Explicit Sub example() Dim REX As RegExp Dim rexMC As MatchCollection Dim rngData As Range Dim arrData As Variant Dim arrSplit As Variant Dim n As Long Dim wks As Worksheet With Sheet1 Set rngData = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With '// Plunk values into array // arrData = rngData.Value '// in case only one cell // If Not IsArray(arrData) Then Exit Sub '// Resize intermediary array // ReDim arrSplit(1 To UBound(arrData), 1 To 2) Set REX = CreateObject("VBScript.RegExp") REX.Pattern = "([A-Za-z]+)([0-9]+)" For n = 1 To UBound(arrSplit) If REX.Test(arrData(n, 1)) Then '// Rip letters and numbers seperate // Set rexMC = REX.Execute(arrData(n, 1)) arrSplit(n, 1) = rexMC(0).SubMatches(0) arrSplit(n, 2) = rexMC(0).SubMatches(1) Else MsgBox "Bad Val", 0&, vbNullString Exit Sub End If Next Set wks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) '// Dump, sort, repopulate intermediary array // With wks.Range("A1").Resize(UBound(arrSplit), 2) .Value = arrSplit .Sort .Cells(1), xlAscending, .Columns(2).Cells(1), , xlAscending, , , xlNo, , False, xlSortColumns, xlPinYin arrSplit = .Value End With '// Repopulate values array. // For n = 1 To UBound(arrData) arrData(n, 1) = arrSplit(n, 1) & arrSplit(n, 2) Next '// Dump back into original range // rngData.Value = arrData '// Kill tmp WB // wks.Parent.Saved = True wks.Parent.Close False End Sub

Hope that helps,

Mark

Dazzawm · Apr 6, 2014

I tried that on a practice workbook and got a compile error saying user-defined type not defined and pointed to the variable REX As RegExp?

GTO · Apr 6, 2014

Sorry, I wrote in early-binding. Set a reference to 'Microsoft VBScript Regular Expressions 5.5' or for late binding, change declarations to:

Dim REX As Object
Dim rexMC As Object

Mark

Dazzawm · Apr 6, 2014

Now I have a box with Bad Val in it? Don't go to too much trouble I am sure Micks will do the job, besides I will need a code to sort all columns not just the first column. Thanks GTO.

How Do I Sort Alpha Numerically?

Dazzawm

Well-known Member

Dazzawm

Well-known Member

Excel Facts

MickG

MrExcel MVP

Dazzawm

Well-known Member

Dazzawm

Well-known Member

MickG

MrExcel MVP

Dazzawm

Well-known Member

GTO

MrExcel MVP

Dazzawm

Well-known Member

GTO

MrExcel MVP

Dazzawm

Well-known Member

Similar threads

Forum statistics

Share this page

How Do I Sort Alpha Numerically?

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock