Sorting columns by letter if they start with number first

I have a column with addresses (see attached image). When I sort it does so by number. I want to sort by street name then by number. Only problem is they are all in same column.

Would it be best to use formula to separate the number then street name? If so, how? Or is there simpler way to do advanced sort or filter?

Thanks

sort.png

Many contributors are not able to view .png files on this forum, due to software incompatibilities with some browsers. Attach a sample Excel workbook instead (the FAQ describes how to).

Hope this helps.

Pete

If your data starts in A1, you can put this formula in B1:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

and copy down to get the street name. You can also put this formula in C1:

=--LEFT(A1,FIND(" ",A1)-1)

to get the number, and copy that down. Then highlight the data in columns A to C and click on Data | Sort and use column B as the main sort field followed by column C. After sorting, you can delete columns B and C if you no longer need them.

Hope this helps.

Pete

This works great, thank you!

Glad to be able to help - thanks for the rep, and for marking the thread as SOLVED.

Pete