Formula to sort by road name first, then by house number

Hi all, I've got a bit of an issue with a spreadsheet for work. They want to sort the columns on a report by the road name then by the house number, but the address is recorded as 1 ABC Road for example. I got a formula that mostly works for that, however we have one property that is 150/152 & 154/158 Dave Road and this formula is picking up the & and using that to sort, bringing it to the front of the list. The full addresses are in row 6, and the formula below is in row 7.

=IF(ISERROR(VALUE(LEFT(B6,1))),B6,MID(B6,FIND(" ",B6)+1,LEN(B6)-FIND(" ",B6)))

Row 7 then includes just the road names for most of the properties, except '150/152 & 154/158 Dave Road' which shows as '& 154/158 Dave Road'. I'm currently using row 7 to sort.



The next issue, that the formula doesn't help with, is that they then want each group of properties sorted by number so: 1... 2... 3... 10... 11... not 1... 10... 11... 2... 3... as Excel is doing.

I've uploaded the same spreadsheet twice - once in .xls once in .xlsx using fake data but giving you an idea of the addresses we need to sort and my current formula in row 7 so you can see what that does as I don't know if I explained it well.

If anyone has any ideas at all I'll appreciate it so much. It could be one long formula that we'd just copy and paste into each report or even two separate formula to put the road name in one row and the number in the next, then running a sort function on the roan name row followed by the number row. I've personally hit my limit and I'm not even sure how I created that formula in the first place!

Thanks