Converting irregular Word table to spreadsheet

[Howard]

I am using Office Professional Edition 2003

I want to convert a list of members of a club and their contact details into
a spreadsheet database.

The form of each record in Word is: membership type (in upper case), name,
address, phone number (if any), fax (if any), email (if any), for example

ACTIVE
Bill Gates
Office in the Sky
25 The Long Road
The Big City
State, Code
COUNTRY
Phone: 123 4567 8910
Fax: 123 4567 8910
Email: (e-mail address removed)
Each line in each record terminates with a line break, and each record
terminates with a paragraph mark.

The problem is that the records are not uniform because:
a.. the addresses differ in number of lines
b.. COUNTRY is omitted if not USA
c.. the Phone, Fax and Email details are omitted if none
I want to end up with each type, name, address (single field will be OK),
Phone, Fax and Email each in separate fields.

Any suggestions please?

Howard

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[joel]

I'm not very good at Word VBA, but I can give some suggestions.


1) Move through word document one paragraph at a time
2) work backwards through the paragraph to get email, fax, phone
3) You are then at the last line of the address which is either
country or State. If this line contains a comma it is a state whic
means it is a USA address. If the line doesn't contains a comma it is
country.

 

[Howard]

I'm grateful for the offer Don, but the list is highly confidential.

Could I perhaps send you some specimens of typical records with the data
altered to protect confidentiality? Then perhaps you might be able recommend
a routine to apply to the whole file.

Howard

 

[Howard]

Thanks for the hint Joel, but I'm looking for a routine.

What you are saying seems to suggest that I go manually through each record.
Or are you proposing something more automated?

Howard

 

[joel]

I'm proposing to automate, but wanted to propose the algorithm first.
You said you wanted some suggestions so I didn't know your skill level.

 

[Don Guillett]

Send a file with/without dummy data. I don't care about the data only a LOT
of samples of the layout.