Need to parse raw unclean data from text to excel into individual columns

I purchased a disc of all the condominium associations in the state of IL from the Sec of State.  The disc is basically raw data on a diisc.  Each file contains:
File #
Association Name
Registered Agent
And aywhere from 3-5 Board Members
In most cases the president has an address and the other directors either have an address or say same #405 or #405 same

I was able to get the data off the disc in text format (below find sample).  I need to parse the date into separate coluns in excel or hire someone who can do this very quickly.  Can someone help?   Here's the text sample:
54386362 ADDISON COMMONS CONDOMINIUM ASSOCIATION
RANDY ROSEN
6310 N LINCOLN AVE
CHICAGO IL 60659-0000
PRESIDENT JO ANN WENMOTH (DI) 3607 N NARRAGANSETT #204 CHICAGO 60634
SECRETARY ADELAIDA LOPEZ (DI) SAME #304
TREASURER SOLITA DELAPEZ (DI) 3605 N NARRAGANSETTI #206 CHICAGO 60634
 CDB29A S E C R E T A R Y O F S T A T E RUN DATE 6/04/09
OD 40223 C O N D O M I N I U M D I R E C T O R L I S T PAGE 20
52338042 ADDISON COURT CONDOMINIUM ASSOCIATION
TRUDY DREWISKE
1329 W ADDISON ST BOX 1
CHICAGO IL 60613-0000
PRESIDENT MIKE BALCSIK(DI) 1327 W ADDISON 1B CHICAGO 60613
SECRETARY KATHERINE SPENOS (DI)SAME #3B
TREASURER TRUDY DREWISKE (DI)1319 W ADDISON #3B CHICAGO 60613
DIRECTOR AGNES RANCILO 1323 SAME #1B
51779827 ADDISON LAKE SHORE EAST CONDOMINIUM ASSOCIATION
STUART W KAISERMAN
1640 N WELLS 207
CHICAGO IL 60614-0000
PRESIDENT BARBARA FALLON (DI) 533 W ADDISON CHICAGO 60613
SECRETARY INVOLUNTARY DISSOLUTION 11 01 96
TREASURER BILL COSTELLO (DI) 533 W ADDSION CHICAGO 60613
 CDB29A S E C R E T A R Y O F S T A T E RUN DATE 6/04/09
OD 40223 C O N D O M I N I U M D I R E C T O R L I S T PAGE 21
62967072 ADDISON LAKE SHORE EAST CONDOMINIUM ASSOCIATION
ROBERT B. LEVIN
325 W HURON #415
CHICAGO IL 60614-0000
PRESIDENT TERRY WELCH(DI) 539 W ADDISON #2N CHICAGO 60613
SECRETARY WENDY COFFELT (DI) 531 W ADDISON #1E CHICAGO 60613
TREASURER PAUL RYKIEL (DI) 539 W ADDISON #3S CHICAGO 60613
51823052 ADDISON LAKE SHORE WEST CONDOMINIUM ASSOCIATION

Thank you.
Condo-Full-Text-List.txt
twister12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shahid ThaikaSole ProprietorCommented:
Well, this is going to be very tedious unless you know the actual format in which the text is formatted. You will need to know VBA, or the person you are hiring should. I noticed the text " CDB29A S E C R E T A R Y O F S T A T E RUN DATE 6/04/09" repeats at every block. You could load the entire text in a string variable and split it into arrays using the above text as a delimiter. Then loop through each string array element and further split them into arrays using a new line character as the delimiter. Now if all the blocks follow the same formatting, then you can retrieve the info using index numbers.
0
Saqib Husain, SyedEngineerCommented:
I have done it for you in this file.

CDB29A.xls
0
twister12Author Commented:
ID: 28946985
ssaqibh

This was a resourceful beginning, however I need to now parse this data into columns on the attached spreadsheet.  I do not need the first two lines of the sections you created starting CD & OD.  Also, you may have noticed that each section you created contains two separte files which would need to be parsed out separate from the other file in that box.

I have attached the spreadsheet I need to parse the data into, and as in my orginal question, when it comes to the directors, in need to get the address from the President's section also into any Director that says same, while making sure if a unit number is listed before or after same, it appears after the address.  I provided an example on the spreadsheet.

The only section I do not need separated is the first name and address which is the Registered Agent.  This canbe all on one line, but ok if easier to separate.

Any assistance you can give me will be appreciated.

Thank you.


Master-List.xlsx
0
Saqib Husain, SyedEngineerCommented:
Check out this file. When you run the macro it will populate sheet2. But there are numerous deviations would be seen which need to be addressed mannually. Some of them might be done by code but it appears too much work for the size of data.

Saqib

P.S. EE does not accept .xlsm files so I have renamed it to .xls. You will have to rename it back to .xlsm to be able to use it.
Condo.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
twister12Author Commented:
Regarding the below item, please accept my apologies.  I was out of the country and fell extremely ill and just returned a week ago and have been playing catch up.

Prior to seeing this comment, I sent positive feedback and want to add that the results I had received as of just before leaving town were beyond excellent.  If not already done so, I would like this expert to receive the highest points possible.

Sincerely,

David Shwarts
shwartsd@gmail.com


leonstryker:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:
Accept: ssaqibh (http:#29165495)

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit.  If no one objects, this question will be closed automatically the way described above.

leonstryker
Experts-Exchange Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.