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

Hi,

Can you confirm that version of Excel in your profile?

Regards

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Administrative Note

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. Thanks.

Hi, we're using Excel For The Web - the free online version. It says Build 16.0.14613.35903 if that helps.

Hi, thank you for pointing that out to me. I've managed to attached the file now!

So your version is 365 for Web Apps - yes? Please update to include 365. Thanks.

The .xls file extension is now ootdated and will not load for some members. Please save the workbook as .xlsx and post it here again. Thanks.

Yes, we can open it, but the forum software can't handle it, meaning that anyone trying to download it will get an error message! Thanks for the .xlsx.

Yes, it was hard enough trying to work out how to word what was requested of me in English, let alone try to come up with the formula! I hadn't thought of an exceptions list. That might be the best way to go forward. Thank you for the suggestion and the formula. I'll give it a try when I get a bit of time.