| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help! How to sort addresses by street then by #......
Hi
Assuming your data is in column A, create 2 helper columns. In the first of the helper columns, format the cells FormatCellsNumberCustom000 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" wrote in message . .. (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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I sort e-mail addresses? | Excel Discussion (Misc queries) | |||
| sort a column of e-mail addresses by domain | Excel Discussion (Misc queries) | |||
| How to Sort Invalid Email Addresses? | New Users to Excel | |||
| How do I sort addresses in excel? | Excel Worksheet Functions | |||
| How do I sort a column of street number/street name by the stree. | Excel Worksheet Functions | |||
Linear Mode
