VBA Required to split addresses

[amb2301]

Hi Friends,
i have a huge list of Addresses, which has to seperated like DELIMITER, but we have some conditions to be applied on it, so i m using vba to split these combined addresses,
I have attached the sample workbook, which contains the Addresses list,
here i have splitted some addresses manually (Higlighted in Yellow) for sample.

Could someone please help me with the VBA to split this addresses as per that sample.

Thanks in Advance.

[Dante Amor]

Check if the following helps you.

Please Login or Register  to view this content.

I have doubts about how to split the addresses that start with a letter, in the end only 5 addresses resulted, I guess you can do it manually.

Address
Highway 16 West
Hwy 16A W
RR 63
Box 518 1011 Hwy 9 South
Bay 3 2200 S Hwy Dr SE

[amb2301]

Thank you so much for your Help Dante Amor !!!
it worked perfectly

yes, addresses start with Alphabets comes in rare case, so i will do it manually ,


i need 1 Help,


below i have attached the screenshot shows the Addresses, which come out wrongly,
i have highlighted those addresses in Red colour & attached the Excel file. Could you please help me on that part.
Actually, in red higlighted cells, Unit range & Door# should be shifted one column more.....issue may be due to length of the street name.


Thanks in Advance.