Sorting addresses in Excel 2

[BTon15]

I have a spreadsheet with a list of names and addresses. The addresses are of the form:

42 High Street
12 Bank Road
8 High Street
...

Is there an easy way to sort the column in Excel so that they are ordered alphabetically?
e.g. I want to see the row with Bank Road before the ones for High Street.

Even better would be a sort that could do alphabetical, then sort by house number within the same street.

Ideally I'd like to be able to do this without a very complicated macro that I probably wouldn't understand!

Thanks very much in advance to anyone who might be able to help.

 

[bluedragon2]

Do a text to columns with a space delimiter, then sort on street then number.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[PeterMoran]

Hi Bton15,

This does not need a macro and is not very difficult.

Highlight all your addresses, and then select Data, Text to Columns, and select Delimited in the Window, and then Next.

In Window 2 select Space as the delimiter, and select "Treat consecutive delimiters as one". You should see your data selected into three columns in Data Preview, and select Next.

In Window 3 select General as the data format for the first column, and then click on the box with the arrow at the end of the Destination address, and then move your cursor to the first of the three columns you will use for the sorting address, and then click on the arrow on the window again and you will be returned to Window 3 with the address you selected. Then select Finish and you have three columns of new sortable addresses ready to be sorted.

I would strongly suggest you use columns close to the original address column, and the rows must match the original addresses.

Now give your new columns titles such as Number, Name, and Type, or something similar.

Finally select all the data you want to sort, which will include your original addresses, as well as your new columns, and including the titles. Now use Data Sort and select "My list has" - Header Row. Then select the Titles from the new columns - under "Sort by" select Name (ascending), and "Then by" select Number (ascending), and now select OK. Your new addresses will be sorted, and so will your original addresses.

Your original addresses should now be in the sequence you requested.

Good Luck!
Peter Moran

 

[BTon15]

Thanks guys - this works just as I wanted.