Sort Text and Numbers:

Try the following code. Note that it assumes the data to be sorted have been selected and the car names are in the first column of the selected data.
[vba]
Dim other As String
Const BigN As Long = 99999

Sub SpecialSortSelection()
Dim r As Range, stuff() As Variant, i As Integer, j As Integer, x As Variant, k As Integer
Dim nr As Integer, nc As Integer
Set r = Selection
nr = r.Rows.Count
If nr = 1 Then Exit Sub
nc = r.Columns.Count
ReDim stuff(1 To nr, 1 To nc + 1)
For i = 1 To nr
stuff(i, 1) = num(r(i, 1))
stuff(i, 2) = other
For j = 2 To nc
stuff(i, j + 1) = r(i, j)
Next j
Next i
For i = 1 To nr - 1
For j = i + 1 To nr
If stuff(i, 1) > stuff(j, 1) Or _
(stuff(i, 1) = stuff(j, 1) And stuff(i, 2) > stuff(j, 2)) Then 'swap
For k = 1 To nc + 1
x = stuff(i, k)
stuff(i, k) = stuff(j, k)
stuff(j, k) = x
Next k
End If
Next j
Next i
For i = 1 To nr
If stuff(i, 1) = BigN Then
r(i, 1) = stuff(i, 2)
Else
r(i, 1) = stuff(i, 1) & stuff(i, 2)
End If
For j = 2 To nc
r(i, j) = stuff(i, j + 1)
Next j
Next i
End Sub

Function num(c As Range) As Long
Dim x As String, i As Integer
other = c
If IsEmpty(c) Then
num = BigN
Exit Function
End If
Do
x = x & Left(other, 1)
If IsNumeric(x) Then
other = Mid(other, 2)
Else
x = Left(x, Len(x) - 1)
If Len(x) = 0 Then x = BigN
num = x
Exit Function
End If
Loop While Len(other) > 0
num = x
End Function
[/vba]

If you have Change Event macros that are working on this data, you will need to disable them while the macro is running.

By formula

data in Col.A and sorted ascending order before insert formula

B1:
=IF(ISERROR(--LEFT(A1,COUNT(-LEFT(A1,COLUMN($1:$16))))),ROW()+9^10,--LEFT(A1,COUNT(-LEFT(A1,COLUMN($1:$16)))))

holding down Ctrl+Shift keys and Enter to get out of the formula bar
then drag down

then sort by Col.B

will give you what you desire, I hope

Jindon

Re: Sort Text and Numbers:

I know this thread is pratically one year old. But it's such a huge and great macro, Derk posted here for sorting one colomn, that I needed to ask another question about it. Is there a way to make this macro work with two columns? The first column has regular 6 digits numbers (always that) and the second colomn uses actually this macro to sort the data (101 102a etc..).

I'd like my data to be that way :

Colomns 1 and 2 ( I have put a vertical line to separate the 2 colomns for this example)

101200 | 101
101200 | 102a
101200 | 102b
101200 | 103
112555 | 12
112555 | 13b
112555 | 14

You see that the second sorting depends of where the related data of the first row is sorted. Any ideas on how I could adapt that vba macro?

Thx!

Werner

great answer

Re: Sort Text and Numbers:

Werner,
The following is a slight modification to the previous code that handles possible alpha suffixes to numbers in the second column. It works on the selection and assumes there are no headers. The Num function is unchanged, but I included it again for completeness.
[vba]Dim other As String
Const BigN As Long = 99999

Sub SpecialSortSelection2()
Dim r As Range, stuff() As Variant, i As Integer, j As Integer, x As Variant, k As Integer
Dim nr As Integer, nc As Integer
Set r = Selection
nr = r.Rows.Count
If nr = 1 Then Exit Sub
nc = r.Columns.Count
ReDim stuff(1 To nr, 1 To nc + 1)
For i = 1 To nr
stuff(i, 1) = r(i, 1)
stuff(i, 2) = num(r(i, 2))
stuff(i, 3) = other
For j = 3 To nc
stuff(i, j + 1) = r(i, j)
Next j
Next i
For i = 1 To nr - 1
For j = i + 1 To nr
If stuff(i, 1) > stuff(j, 1) Or _
(stuff(i, 1) = stuff(j, 1) And stuff(i, 2) > stuff(j, 2)) Then 'swap
For k = 1 To nc + 1
x = stuff(i, k)
stuff(i, k) = stuff(j, k)
stuff(j, k) = x
Next k
End If
Next j
Next i
For i = 1 To nr
r(i, 1) = stuff(i, 1)
If stuff(i, 2) = BigN Then
r(i, 2) = stuff(i, 3)
Else
r(i, 2) = stuff(i, 2) & stuff(i, 3)
End If
For j = 3 To nc
r(i, j) = stuff(i, j + 1)
Next j
Next i
End Sub

Function num(c As Range) As Long
Dim x As String, i As Integer
other = c
If IsEmpty(c) Then
num = BigN
Exit Function
End If
Do
x = x & Left(other, 1)
If IsNumeric(x) Then
other = Mid(other, 2)
Else
x = Left(x, Len(x) - 1)
If Len(x) = 0 Then x = BigN
num = x
Exit Function
End If
Loop While Len(other) > 0
num = x
End Function
[/vba]

Re: Sort Text and Numbers:

Wow, I am truly impressed! Don't be surprised if someone that really knows all the utility of this code says it's godlke hehe! Thank you a lot Derk. As far as I am concerned, this special sort function should be sticked at the beginning of the forum. It's so useful.

Have a good day!

Werner

Re: Sort Text and Numbers:

Could I ask another question that comes in my mind relatively to your code? If those two first colomns containing our previous numbers to sort were attached to information from C To AE. How could I make the two columns move with their information that belongs to them ?

For example :

'It's cells values :


105571	101A	S.I.Q.	V	401-402	2		B	2002	2002-03-01	2009-08-31
106118	101	S.I.Q.		1	3		B	2002	2002-03-01	2009-08-31
106118	102	Charest Inc.	V	2	4	3	E	2003	2002-03-02	2009-09-01
106118	103	Radio-Canada	V	3	5	3	E	2003	2002-03-02	2009-09-01

Re: Sort Text and Numbers:

I thought a bit about it and there are two ways to solve this issue. If I use the two colomns at the beginning as a signature printed elsewhere (or in a virtual array) for the lines C To AE, then I could ask the code to find the new location of the the sorted two columns identification and copy there the C To AE columns that belong to this particular identification.

Or, for the most difficult way, I could try to make the belonging lines to follow their identification columns each time those ones move.

It's not an easy task, cause the code is huge. So, I will try first the first option to see if I can manage that. If you see another way Derk or anyone else who thinks they are up to the challenge (hehe), feel free to show me If I find a way, I'll post it here.

Regards,

Werner

Re: Sort Text and Numbers:

Quote from Werner

Could I ask another question that comes in my mind relatively to your code? If those two first colomns containing our previous numbers to sort were attached to information from C To AE. How could I make the two columns move with their information that belongs to them ?

The code now sorts on the entire selection, so just select the data from C to AE. Column 2 of the selection is "special" in that the code breaks it into two parts (number and text) for the sort and then puts it back together again.

Re: Sort Text and Numbers:

I'll check the code carefully monday morning when I am back at my office. I'll try to select A to AE (A and B are those identification columns, B being as you say the special one). I was kind of dead tired after a big week of work today afternoon. I might have acted like a zombie when I tested the new code. So I must have not done it properly.

I'll send in this thread a feedback monday morning when I am back at work and have access to my Excel file again.

Have a good weekend and again, thank you!

Werner

Re: Sort Text and Numbers:

As I said friday, I post my feedback after a complete testing of the special sorting macro. Well, it works perfectly. It is simply adjustable at will. I modified the function to do an automatic selection of the collumns and rows I needed (it is dynamic for the rows). I post here the modified code for anyone to use it if they need it.

Have a good week Derk!

Werner

Dim other As String
Const BigN As Long = 99999


Sub SpecialSortSelection2()
    
    Dim e As Long


    
' Variable qui sert à savoir s'il y a des PI à supprimer.
Dim Pi_Check As Long
Pi_Check = 0


Dim lstRw As Long


'Vérification s'il y a des PI.
With Worksheets("PI")


    For e = 16 To 25
        If Range("A" & e).Value = "" Then
            'Rien
                
        Else
            Pi_Check = Pi_Check + 1
                    
        End If
    
    Next e
End With
    
If Pi_Check = 0 Then
        
    lstRw = 8


Else
    'Je détermine le nombre de ligne d'unité locative que j'ai.
    Let lstRw = Sheets("PI").Range("a65536").End(xlUp).Row
        
End If


    With Worksheets("PI")
        Range("A8:AF" & lstRw).Select
    End With
    
    Dim r As Range, stuff() As Variant, i As Long, j As Long, x As Variant, k As Long
    Dim nr As Long, nc As Long
    Set r = Selection
    nr = r.Rows.Count
    If nr = 1 Then Exit Sub
    nc = r.Columns.Count
    ReDim stuff(1 To nr, 1 To nc + 1)
    For i = 1 To nr
        stuff(i, 1) = r(i, 1)
        stuff(i, 2) = num(r(i, 2))
        stuff(i, 3) = other
        For j = 3 To nc
            stuff(i, j + 1) = r(i, j)
        Next j
    Next i
    For i = 1 To nr - 1
        For j = i + 1 To nr
            If stuff(i, 1) > stuff(j, 1) Or _
            (stuff(i, 1) = stuff(j, 1) And stuff(i, 2) > stuff(j, 2)) Then 'swap
            For k = 1 To nc + 1
                x = stuff(i, k)
                stuff(i, k) = stuff(j, k)
                stuff(j, k) = x
            Next k
        End If
    Next j
Next i
For i = 1 To nr
    r(i, 1) = stuff(i, 1)
    If stuff(i, 2) = BigN Then
        r(i, 2) = stuff(i, 3)
    Else
        r(i, 2) = stuff(i, 2) & stuff(i, 3)
    End If
    For j = 3 To nc
        r(i, j) = stuff(i, j + 1)
    Next j
Next i


    With Worksheets("PI")
        Range("A6").Select
    End With


End Sub


Function num(c As Range) As Long
    Dim x As String, i As Long
    other = c
    If IsEmpty(c) Then
        num = BigN
        Exit Function
    End If
    Do
        x = x & Left(other, 1)
        If IsNumeric(x) Then
            other = Mid(other, 2)
        Else
            x = Left(x, Len(x) - 1)
            If Len(x) = 0 Then x = BigN
            num = x
            Exit Function
        End If
    Loop While Len(other) > 0
    num = x
End Function

Re: Sort Text and Numbers:

Hi,

Again, going further with this thread. The two preceding special sorting macros were amazing. I would need to push it a little bit farther. My new sheet works in the same principle as a building and its apartments. But this time, it's sectors, which include streets and civic numbers. So here's the detail of what I'd like to achieve. As the original sort macro, I'd like the first column to be able to sort with numbers with string characters at the end (e.g 1, 10b, 13). The second column would need a sort by string (1st avenue, Henri-Bourassa, Xavier Street, Harlington Road, Corronarian Street, 4rth avenue). The third and last column is civic numbers which belong to their respective street. Those streets belong to their respective sector, just like the second special sort principle. I have attached an Excel sample to show what I mean. So, if you could help me again Derk I would be really happy to have this special sort. The two preceding ones were so powerful and useful in my Excel file.

So, hoping to have news from you on that one, I wish you a good beginning of week!

Werner

Re: Sort Text and Numbers:

I believe the first macro in this thread does what you ask. It sorts the selection assuming the first column of the selection may have alpha characters at the end of the entries. Have I misunderstood your question?

Re: Sort Text and Numbers:

A bit yes, hehe. Here's an example of the sort

Sector | Street | Civic number (The "|" are here to seperate columns)

1 | 1st Avenue | 4500
1 | Rose Street | 300
2b | Harlington | 1200
2b | Harlington | 1300
2b | Magelan | 3400
2b | Magelan | 3445
2b | Magelan | 3450
3 | Epic Street | 345
3 | Epic Street | 500
3 | Kingston | 400
3 | Lingston | 450
4 | 3e Avenue | 150
(...)

You see, the first sort macro considers the first column as the one to be sorted and the other columns as other information columns belonging to this first column. This is different here. As you can see, inside the sort of the sector there's a need of a sort of the streets belonging to that sector, and a sort for the civic numbers belonging to their street. By that way, you have buildings grouped in sectors, street and in civic numbers, and you can do a much better analysis and pivot tables.

Re: Sort Text and Numbers:

Try this version that takes a slightly different approach. Again it works on the selection (with no headers) and handles the first column as special. It sorts bythe first three columns of the selction, but that could easily be extended as needed.
[vba]Dim other As String
Const BigN As Long = 99999

Sub SortThis()
Dim r As Range, c As Range
Application.ScreenUpdating = False
Set r = Selection
r.Columns("B:C").Insert xlToRight
For Each c In r.Columns(1).Cells
If IsNumeric(c) Then
c.Offset(0, 1) = c.Value
Else
c.Offset(0, 1) = num(c)
c.Offset(0, 2) = other
End If
Next c
r.Sort key1:=r.Cells(1, 5), order1:=xlAscending, header:=xlNo, Orientation:=xlTopToBottom
r.Sort key1:=r.Cells(1, 2), order1:=xlAscending, key2:=r.Cells(1, 3), order2:=xlAscending, _
key3:=r.Cells(1, 4), order3:=xlAscending, header:=xlNo, Orientation:=xlTopToBottom
r.Columns("B:C").Delete xlToLeft
Application.ScreenUpdating = True
End Sub

Function num(c As Range) As Long
Dim x As String, i As Integer
other = c.Value
If IsEmpty(c) Then
num = BigN
Exit Function
End If
Do
x = x & Left(other, 1)
If IsNumeric(x) Then
other = Mid(other, 2)
Else
x = Left(x, Len(x) - 1)
If Len(x) = 0 Then x = BigN
num = x
Exit Function
End If
Loop While Len(other) > 0
num = x
End Function[/vba]

Re: Sort Text and Numbers:

Derk, it works perfectly! You are like a great Excel wizard (or magician, I don't know the difference between those two words as it means the same thing for me in my French native tongue). I am really happy

Have a good day!

Werner

Re: Sort Text and Numbers:

Hi again Derk,

I didn't know at the time of the last macro, but the first column, sector, it's not number but names of sectors in a city. Sol, actually the macro with sector, street and civic numbers can't work cause it tries to sort numbers in the first column while in it letters. I don't know if you could slightly change the macro to be sort letters in the first column? It would be greatful!

Bye!

Werner

Sector | Street | Civic number (The "|" are here to seperate columns)

Bourg | 1st Avenue | 4500
Charles | Rose Street | 300
Beauport | Harlington | 1200
Beauport | Harlington | 1300
Limoilou | Magelan | 3400
Limoilou | Magelan | 3445
Limoilou | Magelan | 3450
Beauport | Epic Street | 345
Beauport | Epic Street | 500
Beauport | Kingston | 400
Beauport | Lingston | 450
ChateauRicher | 3e Avenue | 150
(...)

Re: Sort Text and Numbers:

From the example, it would appear that a standard Excel sort on the first three columns will do the job. Is that so?