Help! How to sort addresses by street then by #......

(NOTE: PLEASE E-MAIL REPLY DIRECT AS WELL AS TO THE GROUP, AS MY SERVER
OFTEN GOES DOWN, ETC....THANX)

I'm having difficulty sorting addresses for my delivery route.....
From the example pasted below, you can see that Holland St is spread out
throughout the column....I need to group/sort this column so that it would
look like this (example
110 Holland St
111 Holland St
112 Holland St

but instead it looks like this, (below), and as you can see, Holland St is
spread out all over the column!
Help??
thanx:

163 WASHINGTON ST

169 HOLLAND ST

169 HOLLAND ST

169 HOLLAND ST

169 HOLLAND ST

17 HOLLAND ST

17 VILLAGE ST



179 WASHINGTON ST

187 ELM ST

19 UNION SQ

19 WESLEY PARK

193 SOMERVILLE AVE

196 SOMERVILLE AVE

2 UNION SQ

20 HOLLAND ST

20 HOLLAND ST STE 407

20 HOLLAND ST STE 408

20 MEDFORD ST

201 ELM ST

201 HIGHLAND AVE #A

20-40 HOLLAND STREET SUITE 402

20A COLLEGE AVE

21 MCGRATH HWY

21 MCGRATH HWY

21 REV NAZARENO PROPERZI

21 UNION SQ

215 HIGHLAND AVE

2153 MYSTIC VALLEY PKWY

217 HIGHLAND AVE

217 HIGHLAND AVE

22 COLLEGE ST

22 KENT ST

22 KENT ST

22 MCGRATH HWY

22 MCGRATH HWY STE 11

221 MORRISON AVE

225 ELM ST

228 LOWELL ST

229A LOWELL ST

23 BOSTON STREET

23 UNION SQ

230 ELM ST

230 HIGHLAN AVE

233 ELM ST

234 ELM ST

235 HOLLAND ST

Hi

Assuming your data is in column A, create 2 helper columns.
In the first of the helper columns, format the cells
Format>Cells>Number>Custom>000 and enter
=--(left(A1,FIND(" ",A1)-1)
In the second helper column enter
=MID(A1,FIND(" ",A1)+1,255)
Copy both formulae down the extent of your data.
Mark the whole block of your data, then Sort using the second helper
column as the first sort key, and the first helper column as the second
sort key.

--
Regards

Roger Govier


"HelpingOthersTD" <[email protected]> wrote in message
news:[email protected]...
> (NOTE: PLEASE E-MAIL REPLY DIRECT AS WELL AS TO THE GROUP, AS MY
> SERVER OFTEN GOES DOWN, ETC....THANX)
>
> I'm having difficulty sorting addresses for my delivery route.....
> From the example pasted below, you can see that Holland St is spread
> out throughout the column....I need to group/sort this column so that
> it would look like this (example
> 110 Holland St
> 111 Holland St
> 112 Holland St
>
> but instead it looks like this, (below), and as you can see, Holland
> St is spread out all over the column!
> Help??
> thanx:
>
> 163 WASHINGTON ST
>
> 169 HOLLAND ST
>
> 169 HOLLAND ST
>
> 169 HOLLAND ST
>
> 169 HOLLAND ST
>
> 17 HOLLAND ST
>
> 17 VILLAGE ST
>
>
>
> 179 WASHINGTON ST
>
> 187 ELM ST
>
> 19 UNION SQ
>
> 19 WESLEY PARK
>
> 193 SOMERVILLE AVE
>
> 196 SOMERVILLE AVE
>
> 2 UNION SQ
>
> 20 HOLLAND ST
>
> 20 HOLLAND ST STE 407
>
> 20 HOLLAND ST STE 408
>
> 20 MEDFORD ST
>
> 201 ELM ST
>
> 201 HIGHLAND AVE #A
>
> 20-40 HOLLAND STREET SUITE 402
>
> 20A COLLEGE AVE
>
> 21 MCGRATH HWY
>
> 21 MCGRATH HWY
>
> 21 REV NAZARENO PROPERZI
>
> 21 UNION SQ
>
> 215 HIGHLAND AVE
>
> 2153 MYSTIC VALLEY PKWY
>
> 217 HIGHLAND AVE
>
> 217 HIGHLAND AVE
>
> 22 COLLEGE ST
>
> 22 KENT ST
>
> 22 KENT ST
>
> 22 MCGRATH HWY
>
> 22 MCGRATH HWY STE 11
>
> 221 MORRISON AVE
>
> 225 ELM ST
>
> 228 LOWELL ST
>
> 229A LOWELL ST
>
> 23 BOSTON STREET
>
> 23 UNION SQ
>
> 230 ELM ST
>
> 230 HIGHLAN AVE
>
> 233 ELM ST
>
> 234 ELM ST
>
> 235 HOLLAND ST
>
>
>