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

TatumLD

New Member
Joined
Dec 17, 2018
Messages
2
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".

5927 Howe Street Apartment 301 Pittsburgh PA 15232
1540 West 50th Ave Denver, Colorado 80221
1101 West 4th Street Unit 40 Winston Salem, NC 27101
23 Virginia St. Saint-Isadore, NB E8M B2K CANADA
50 Main St. Building 3 Box 49 Portland, Ore. 97210
1921 Banbury Road Kalamazoo, MI 49001
24084 SE 146th Court #33 Beaver PA 15009

<colgroup><col></colgroup><tbody>
</tbody>

Any help would be greatly appreciated.
Tatum
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

TatumLD

New Member
Joined
Dec 17, 2018
Messages
2
FYI - I am printing envelopes with this info so I have to have the City, State, and Zip on one line.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,027
Messages
5,466,114
Members
406,467
Latest member
bpat83

This Week's Hot Topics

Top