[FONT=lucida console][color=Royalblue]Sub[/color] a1086751b()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086751-help-sort-road-addresses-complex-sort-scenario.html[/color][/i]
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color], j [color=Royalblue]As[/color] [color=Royalblue]Long[/color], n [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, vb, x, q, y, p, z
Application.ScreenUpdating = [color=Royalblue]False[/color]
n = Range([color=brown]"A"[/color] & Rows.count).[color=Royalblue]End[/color](xlUp).Row
va = Range([color=brown]"A1:A"[/color] & n)
[color=Royalblue]ReDim[/color] vb([color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]2[/color])
[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
z = va(i, [color=crimson]1[/color])
x = Split(z, [color=brown]" "[/color])
[color=Royalblue]If[/color] IsNumeric(Left(z, [color=crimson]1[/color])) [color=Royalblue]Then[/color]
[color=Royalblue]For[/color] j = LBound(x) + [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(x)
vb(i, [color=crimson]1[/color]) = vb(i, [color=crimson]1[/color]) & [color=brown]" "[/color] & x(j)
[color=Royalblue]Next[/color]
vb(i, [color=crimson]2[/color]) = x([color=crimson]0[/color])
[color=Royalblue]Else[/color]
[color=Royalblue]If[/color] InStr(z, [color=brown]","[/color]) [color=Royalblue]Then[/color]
y = Split(z, [color=brown]","[/color])
p = Split(y([color=crimson]1[/color]), [color=brown]" "[/color])
vb(i, [color=crimson]2[/color]) = p([color=crimson]1[/color])
[color=Royalblue]For[/color] j = LBound(p) + [color=crimson]2[/color] [color=Royalblue]To[/color] UBound(p)
vb(i, [color=crimson]1[/color]) = vb(i, [color=crimson]1[/color]) & [color=brown]" "[/color] & p(j)
[color=Royalblue]Next[/color]
[color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]
[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(vb, [color=crimson]1[/color])
z = vb(i, [color=crimson]1[/color])
[color=Royalblue]If[/color] InStr(z, [color=brown]","[/color]) [color=Royalblue]Then[/color]
vb(i, [color=crimson]1[/color]) = Split(z, [color=brown]","[/color])([color=crimson]1[/color])
[color=Royalblue]End[/color] [color=Royalblue]If[/color]
vb(i, [color=crimson]1[/color]) = Trim(vb(i, [color=crimson]1[/color]))
z = vb(i, [color=crimson]2[/color])
[color=Royalblue]If[/color] [color=Royalblue]Not[/color] IsNumeric(z) [color=Royalblue]Then[/color]
vb(i, [color=crimson]2[/color]) = Left(z, Len(z) - [color=crimson]1[/color])
[color=Royalblue]If[/color] IsNumeric(vb(i, [color=crimson]2[/color])) [color=Royalblue]Then[/color] vb(i, [color=crimson]2[/color]) = vb(i, [color=crimson]2[/color]) + [color=crimson]0.1[/color]
[color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]
Range([color=brown]"D1"[/color]).Resize(UBound(vb, [color=crimson]1[/color]), [color=crimson]2[/color]) = vb
Range([color=brown]"A1:E"[/color] & n).Sort Key1:=Range([color=brown]"C1"[/color]), order1:=xlAscending, Key2:=Range([color=brown]"D1"[/color]), order2:=xlAscending, Header:=xlNo
[i][color=seagreen]'Range("D1:E" & n).ClearContents[/color][/i]
Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]