Extracting street name and house number from address list

Hi,
I need to separate the house number and street name from a list of addresses.
My address list contains about 5000 entries showing house number, and street name (sometimes Flat number) in various and inconsistent formats with the house number being both in front of the street name and behind the street name, sometimes separated with commas and othertimes not.
I have inserted the different types/format of address into the attached worksheet and in column B have shown the addresses in their original formats. I would like to separate the address into number and street name as I have done manually in Columns C and D but would appreciate some assistance/ advice or preferably a formula that I can use.
Many thanks
Mikey

Hi Winon,
Thanks for responding. As an alternative it would help if I could get all the street names in order. To reduce the number of permutations, would you be able to help with a formula that would separate the number and comma (or two commas and no commas!) before the street name. I would then have a column with the street name and the adjacent column with the full addresse in the various formats as have shown in my modified sample attachment, where the three different types of address format with number before street name is shown.
Thanks
Mikey

Hi Winon
Looks like I have no alternative but to do it manually.
Thanks and regards
Mikey

Hi Haseeb
Have been away and what a brilliant surprise to see your solution, you are an absolute genius and can't thank you enough, also
Winon for your perseverence. Thanks again to you both.
Best regards
Mikey

Sorry forgot to mention you Jindon, only saw Haseebs post initially, thanks again to you both
Rgds
Mikey

Hi Jindon,
Just to let you know I put my whole address range consisting of 1869 addresses in the column and it sorted 1863 out,
I didnt worry too much about the other six but am so very grateful for your solution.
Best regards
Mikey

No worries.

If you have any situation that the code doesn't pick up the correct results, just post back with the data and the desired result...

Hi Jindon
As I said 99.8% of all my address formats are recognised by your macro, The ones that are not are included on the attached, Also I have one or two addresses that are a name and a street eg Seaview, Atlantic Av which I can manage manually. I am so happy with the results as they are but if you want to have a look at the addresses that are not picked up by your code they are on the attached.
Thanks again
Mikey

Try change to

Please Login or Register  to view this content.

Hi Jindon,
Am not at all familiar with macros and dont really know what I have done and whether I have done it properly but pressed "click" and the three outstanding addresses separated correctly. I then input my whole address range and got a 100% separation. For your info (and Winton's if you read this) I have manually separated about 2800 addresses during the past week and in a second separated the remaining 1869. No more manual separation!!
Thank you again
Mikey

Glad it worked and thanks for the feedback.