Converting irregular Word table to spreadsheet
in [General]
|
Prev: Date Format
Next: unhide all columns
From: Howard on 25 Apr 2010 07:35 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: bill(a)heaven.org 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
From: Don Guillett on 25 Apr 2010 08:11 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. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Howard" <man_o_law(a)hotmail.com> wrote in message news:u9uzHyG5KHA.5808(a)TK2MSFTNGP02.phx.gbl... >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: bill(a)heaven.org > 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 > >
From: joel on 25 Apr 2010 08:16 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 a country or State. If this line contains a comma it is a state which means it is a USA address. If the line doesn't contains a comma it is a country. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198300 http://www.thecodecage.com/forumz
From: Howard on 25 Apr 2010 08:49 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 "Don Guillett" <dguillett1(a)gmail.com> wrote in message news:%23$a4mBH5KHA.1424(a)TK2MSFTNGP04.phx.gbl... > 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. > > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "Howard" <man_o_law(a)hotmail.com> wrote in message > news:u9uzHyG5KHA.5808(a)TK2MSFTNGP02.phx.gbl... >>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: bill(a)heaven.org >> 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 >> >> > >
From: Howard on 25 Apr 2010 08:52
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" <joel.49z4ie(a)thecodecage.com> wrote in message news:joel.49z4ie(a)thecodecage.com... > > 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 a > country or State. If this line contains a comma it is a state which > means it is a USA address. If the line doesn't contains a comma it is a > country. > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 > View this thread: > http://www.thecodecage.com/forumz/showthread.php?t=198300 > > http://www.thecodecage.com/forumz > > |