Sorting addresses with numbers and text

[bjkiwi007]

Hi there,
I am trying to sort a column with both numbers and text. For example, 1, 10, 1a, 2a, 2b etc. These are address numbers and I want to sort firstly by street name and then by house number. The street name part works fine but every time I sort the street number column I get 1, 4, 6, 1a, 10a etc. Can some one please help me in sorting this so that the number 1 is always at the top regardless if it has an a or b behind it. eg; 1, 1a, 1b, 2, 4, 6, 6a, 6b etc..
Thanks in advance and any help would be greatly appreciated.
BJkiwi007
Example below. As you can see, 10a and 8a are at the bottom but I want them to be after 8 and 10 respectively.
<table border="0" cellpadding="0" cellspacing="0" width="104"><colgroup><col style="mso-width-source:userset;mso-width-alt:1901; width:39pt" span="2" width="52"> </colgroup><tbody><tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">1</td> <td class="xl66" style="width:39pt" width="52">1</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">2</td> <td class="xl66" style="width:39pt" width="52">2</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">3</td> <td class="xl66" style="width:39pt" width="52">3</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">4</td> <td class="xl66" style="width:39pt" width="52">4</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">5</td> <td class="xl66" style="width:39pt" width="52">5</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">7</td> <td class="xl66" style="width:39pt" width="52">7</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">8</td> <td class="xl66" style="width:39pt" width="52">8</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">9</td> <td class="xl66" style="width:39pt" width="52">9</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">10</td> <td class="xl66" style="width:39pt" width="52">10</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">11</td> <td class="xl66" style="width:39pt" width="52">11</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">12</td> <td class="xl66" style="width:39pt" width="52">12</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">14</td> <td class="xl66" style="width:39pt" width="52">14</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">15</td> <td class="xl66" style="width:39pt" width="52">15</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">16</td> <td class="xl66" style="width:39pt" width="52">16</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">18</td> <td class="xl66" style="width:39pt" width="52">18</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">22</td> <td class="xl66" style="width:39pt" width="52">22</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">24</td> <td class="xl66" style="width:39pt" width="52">24</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">26</td> <td class="xl66" style="width:39pt" width="52">26</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">28</td> <td class="xl66" style="width:39pt" width="52">28</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">10a</td> <td class="xl66" style="width:39pt" width="52">10a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">8a</td> <td class="xl66" style="width:39pt" width="52">8a</td> </tr> </tbody></table>

 

[gsbelbin]

You need all your numbers to be text. One way to do this is to use the Data , Text to Columns menu item. Select the column of data, then select Data, Text to columns. Click Next twice and then check the radio button next to Text in the Column Data Format box. Then click Finish. The data will sort as you want now. It will prompt you for how you want sort. Choose "Sort numbers and numbers stored as text separately".

 

[AlphaFrog]

I was also going to suggest the same thing as gsbelbin. You will get this result though.

1
10
10a
11
12
14
15
16
18
2
22
24
26
28
3
4
5
7
8
8a
9

Not exactly what you requested, but maybe it works for you.

 

[bjkiwi007]

Thanks for your reply. I have done as you suggested but this is the result i got. As you can see 2 should be before 10 as well as 3 and 4. Did I go wrong somewhere?
<table border="0" cellpadding="0" cellspacing="0" width="52"><colgroup><col style="mso-width-source:userset;mso-width-alt:1901;width:39pt" width="52"> </colgroup><tbody><tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">10</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">11</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">15</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">17</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">19</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">2</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">21</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">23</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">25</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">26</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">3</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">4</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">5</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">6</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">7</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">9</td> </tr> </tbody></table>

 

[bjkiwi007]

Can no one help me out on this one?

 

[bjkiwi007]

A bit of extra info:
The address numbers go up to 389. Would I have to make a custom list like this?
1a,1b,1c,2a,2b,2c,3a,3b,3c etc... up to 389?
I have done this through to 20 before I asked on this forum but I thought it is way too much work and that there had to be an easier way.
Thanks in advance

 

[MickG]

Try this:-
Results column "C".

[COLOR=navy]Sub[/COLOR] MG28Apr38
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[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] tNum [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] temp [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] temp2 [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
Ray = Rng.Resize(, 2).value
[COLOR=navy]For[/COLOR] n = 1 To UBound(Ray)
[COLOR=navy]If[/COLOR] Not IsNumeric(Right(Ray(n, 1), 1)) [COLOR=navy]Then[/COLOR]
        tNum = Right(Ray(n, 1), 1)
        Ray(n, 1) = Left(Ray(n, 1), Len(Ray(n, 1)) - 1)
        Ray(n, 2) = tNum
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]For[/COLOR] i = 1 To UBound(Ray)
    [COLOR=navy]For[/COLOR] j = i To UBound(Ray)
        [COLOR=navy]If[/COLOR] Val(Ray(j, 1)) < Val(Ray(i, 1)) [COLOR=navy]Then[/COLOR]
            temp = Val(Ray(i, 1))
            temp2 = Ray(i, 2)
                Ray(i, 1) = Ray(j, 1)
                Ray(i, 2) = Ray(j, 2)
                    Ray(j, 1) = temp
                    Ray(j, 2) = temp2
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] j
[COLOR=navy]Next[/COLOR] i
ReDim nRay(1 To UBound(Ray))
    [COLOR=navy]For[/COLOR] n = 1 To UBound(Ray)
        [COLOR=navy]If[/COLOR] Not Ray(n, 2) = vbNullString [COLOR=navy]Then[/COLOR]
            nRay(n) = Ray(n, 1) & Ray(n, 2)
        [COLOR=navy]Else[/COLOR]
            nRay(n) = Ray(n, 1)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
Range("C1").Resize(Rng.Count) = Application.Transpose(nRay)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[bjkiwi007]

Thanks Mick for your reply. That copies my village names over my street address names. My house numbers are in the column D if that helps?

 

[sulakvea]

hi, you shouldnt have too many "a"'s and "b"'s in the list right? say you have 10 at most - like a, b, c, aa, etc.

replace "a" (i.e. in the entire column, with ctrl+h) with .01 (make sure you spell it that way, i.e. replace "a" with .01, not 0.01). replace "b" with .02, etc. So now your 10a will become 10.01, i.e. a number, and once sorted - will end up right after 10

 

[SteveO59L]

Assuming your list is in col A

in C1 and copied down

=IF(CODE(RIGHT(A1,1))<58,A1,LEFT(A1,LEN(A1)-1))

seperates the numbers and text.

in D1 and copied down

=IF(LEN(C1)=1,TEXT(C1,"00#"),IF(LEN(C1)=2,TEXT(C1,"0##"),TEXT(C1,"###")))

Formates the numbers as text with leading zeros.

in e1 and copied down.

=IF(CODE(RIGHT(A1,1))>57,RIGHT(A1,1),"")

extracts the text part of the address.



Then sort by col D