Macro to Split After "Apartment [0-9]"?

[TatumLD]

Hello, I receive address files from clients that are rarely formatted properly and usually have all their data pasted in 1 column. (See example below) I've made a macro that adds "|" at the end of Street, Ave, Road, etc and then splits into different columns but I can't figure out how to split again with things like "Apartment 301" or "Building 3 Box 49".

[TABLE="width: 446"]
<colgroup><col></colgroup><tbody>[TR]
[TD]5927 Howe Street Apartment 301 Pittsburgh PA 15232[/TD]
[/TR]
[TR]
[TD]1540 West 50th Ave Denver, Colorado 80221[/TD]
[/TR]
[TR]
[TD]1101 West 4th Street Unit 40 Winston Salem, NC 27101[/TD]
[/TR]
[TR]
[TD]23 Virginia St. Saint-Isadore, NB E8M B2K CANADA[/TD]
[/TR]
[TR]
[TD]50 Main St. Building 3 Box 49 Portland, Ore. 97210[/TD]
[/TR]
[TR]
[TD]1921 Banbury Road Kalamazoo, MI 49001[/TD]
[/TR]
[TR]
[TD]24084 SE 146th Court #33 Beaver PA 15009[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated.
Tatum

 

[TatumLD]

FYI - I am printing envelopes with this info so I have to have the City, State, and Zip on one line.