Sort alpha-numeric addresses by the number only?

[Rich_B]

Hi

I have a list of addresses that currently sort as follows:

1 Acacia Road
10 Acacia Road
11 Acacia Road
2 Acacia Road
21 Acacia Road
22 Acacia Road
3 Acacia Road
31 Acacia Road
etc.

How can I get them to sort in ascending order of the number i.e. 1, 2, 3, 10, 11, 21, 22, 31 etc

I have tried changing the data type to 'text', 'general', 'number' etc but I cannot get it to sort in the way I would like.

Any help much appreciated.

Thank you.

Excel 2013
Win 10
PC

 

[MickG]

Re: How to sort alpha-numeric addresses by the number only?

This is one way, for data in column "A" starting "A1".

[COLOR="Navy"]Sub[/COLOR] MG05Sep14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] I, J, Temp1, Temp2, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 2)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn.Value, " ")
c = c + 1
ray(c, 1) = Sp(0): ray(c, 2) = Right(Dn.Value, Len(Dn.Value) - (Len(Sp(0)) + 1))
[COLOR="Navy"]Next[/COLOR] Dn
[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]
            Temp1 = ray(I, 1)
            Temp2 = ray(I, 2)
                ray(I, 1) = ray(J, 1)
                ray(I, 2) = ray(J, 2)
            ray(J, 1) = Temp1
            ray(J, 2) = Temp2
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] J
[COLOR="Navy"]Next[/COLOR] I
[COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
    Range("A" & n).Value = ray(n, 1) & " " & ray(n, 2)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[JackDanIce]

Re: How to sort alpha-numeric addresses by the number only?

One suggestion:

Use a helper column, to the right of the data to extract the number part
Suggestion of a formula to do this:

=VALUE(LEFT(A1,FIND(" ",A2)-1))

(assuming it's always at the start of the address, followed by a space)
Drag formula to last row with data, then select all data and sort by this column, ascending
Delete the helper column

 

[Rich_B]

Re: How to sort alpha-numeric addresses by the number only?

Thanks both.

 

[JackDanIce]

Re: How to sort alpha-numeric addresses by the number only?

You're welcome