how to sort addresses so 111 appears before 1000 ?

[link626]

I have a list of addresses in a column as such in excel 2003.
How do I make excel sort it in proper numerical order such that 115 appears before 1111 instead of following 1133 ?

111 S main st
1111 S main st
1119 S main st
1124 S main st
1125 S main st
1130 S main st
1133 S main st
115 S main st
1203 S main st
1211 S main st
1215 S main st
1218 S main st
1219 S main st
1220 S main st
1222 S main st
1227 S main st
1234 S main st
125 S main st
1288 S main st
1308 S main st
1312 S main st
1319 S main st

[oeldere]

Format the cells in equal numbers so 111 => will be 0111 and so on.

[estige]

I would split number from text in helper columns, and sort on these.
With first address in A2:
B2: =LEFT(A2,SEARCH(" ",A2)-1)
C2: =SUBSTITUTE(A2,B2&" ","",1)
copy down

[Special-K]

This extracts a number from a string

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$4),1)),0),COUNT(1*MID(A1,ROW($1:$4),1)))

copy down and sort on that.

Source: http://office.microsoft.com/en-gb/ex...001154901.aspx

[Richard Buttrey]

... or use Data Text to Columns Fixed width and split at the 5th character then sort the two resulting columns. If necessary concatenate the two cells on each row back to a single cell.