General discussion

Locked

sorting address

By BarryVG ·
I have a user who needs to sort a mailing list by the address. However, he needs to sort by the street name and not include the street number. Anyone have an idea on how to do this. Custom list possibly?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

sorting address

by timwalsh In reply to sorting address

In what form is the address list at present (Excel spreadsheet, Access database, Outlook contact list, other)?

Outlook will only sort on email alias. You could export the contac list to a spreadheet, but you would still have the problem of street name and number in the same field.

You could only sort on the street name separately from the street number (in either Excel or Access) if street number and street name exist as separate fields.

So yes, you would need to build a custom list separating these 2 parts of the address.

Collapse -

sorting address

by BarryVG In reply to sorting address

In Excel, and I used the solution in Answer 2

Collapse -

sorting address

by DKlippert In reply to sorting address

With Excel, it's a small mater to select the addresses and then use Data>Text to Columns to seperate the address at the spaces.
They can then be sorted and concatenated
=A1&" "&B1&" "&C1
Then copied and Paste Special Values to create a new list.

Collapse -

sorting address

by BarryVG In reply to sorting address

This worked very well. It was easy to review the changes and make the one or two editting requirements afterwards. Saved a lot of time. Thanks

Collapse -

sorting address

by RichTee In reply to sorting address

If you could accomplish ?a small mater to select the addresses and then use Data>Text to Columns to seperate the address at the spaces? in answer #2, it would be a snap. However, I am not sure how you would handle all of the following addresses.1235 Almond Street
1235 C Almond Street
1235 ? Almond Street
1235 B Drive
1235 Almond Joy Lane
123 Butter Avenue
10125 Long Road

I am very interested and I have subscribed to this question as to how this will work.

Collapse -

sorting address

by RichTee In reply to sorting address

BigPaul (answer 4), very good. I tried your solution and it works with everything but the Alphabet addresses, e.g. 1235 K Street.
All in all, this should resolve 99% of the problem. Rich

Collapse -

sorting address

by BarryVG In reply to sorting address

Used answer from DKlippert. It worked like a breeze. Minor editting to take care of the uniques, but simple process

Collapse -

sorting address

by Big Paul In reply to sorting address

Working with the list in answer 3.

Copy that list into a spreadsheet from cells b2 to b8
Past this formula in cells c2 to c8 =SEARCH(" ",B2)
Paste this formula in cells d2 to d8 =SEARCH(" ",B2,C2+1)
Finally paste this formula into cells e2to e8 =IF(D2-C2=2,RIGHT(B2,LEN(B2)-D2),RIGHT(B2,LEN(B2)-C2))

now block b2 to e8 and sort on column e.

A similar solution would work in Access. In essence look for the first two spaces. Trim dependant if they are 2 charactures apart or not.
Regards

Paul

Collapse -

sorting address

by BarryVG In reply to sorting address

Using solution in Answer 2 was a simpler approach. But thanks for your input.

Collapse -

sorting address

by BarryVG In reply to sorting address

This question was closed by the author

Back to Software Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums