COMMON PROBLEM split name, address, city, state and zip

Hello,

This is a common problem I believe many people face is to automate a process that split name, address, city, state, and zip.

here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN.

911EP AQUISITION CORP ---------------row 1
13386 INTERNATIONAL PKWY------------row2
JACKSONVILLE, FL 32218---------------row3


911EP AQUISITION CORP ---------name
13386 INTERNATIONAL PKWY----------address
JACKSONVILLE--------------------city
FL -------------------------------state
32218---------------------------zip


A & P HARDWARE & BUILDING-----------row1
SUPPLY COMPANY----------------------row2
2757 OZARK AVE-----------------------row3
GASTONIA, NC 28052------------------row4


A & P HARDWARE & BUILDING SUPPLY COMPANY----------name
2757 OZARK AVE----------------------------------------address
GASTONIA---------------------------------------city
NC ------------------------------------------------state
28052--------------------------------------------zip


A&R METAL INDUSTRIES LTD---------------row1
2020 #6 RD-------------------------------row2
RICHMOND, BRITISH COLUMBIA V6V--------row3
1P1--------------------------------------row4
CANADA----------------------------------row5


A&R METAL INDUSTRIES LTD-----------------name
2020 #6 RD------------------------------address
RICHMOND ----------------------------------city
BRITISH COLUMBIA --------------------------state
V6V 1P1--------------------------------------zip
CANADA--------------------------------country


ZUKEN USA INC 238 LITTLETON RD STE 100 WESTFORD, MA 10886 ----------all in one row


ZUKEN USA INC------------------------------name
238 LITTLETON RD STE 100 -----------------address
WESTFORD--------------------------------city
MA --------------------------------state
10886-------------------------------zip

can any process in excel be automated even if the datas are not consistent and you don't know how you gonna get them from time to time?

if someone know how to automate this task or know of a software then let me know. I need this to be done with 100% accuracy with a given of about 30000 datas, is this possible?

NOTE: or very close to the 100%

http://www.mrexcel.com/forum/showthread.php?t=313985

thanks

thanks, here it is.

thanks, could you send/post it so i could have a look at it.

just timed myself doing it manually 8 mins to do 5
thats just about 100days work!@8 hrs a day
wish you luck but it does look a bit beyond something excel can do because of so many different formats