Database Text File Problem - Macro?

[Joe_Toz]

I am hoping that someone out there might be able to help or point me in the right direction. I currently have a very large text file (generated via Omnipage 15 Pro) of information that must be used to create a customer database. Ideally, the database would have the following fields:
Column1: Indicator (a plus or minus sign, numeric value, etc.)
Column2: Last Name
Column3: First Name
Column4: Year
Column5: Street Address
Column6: City
Column7: State
Column8: Zip
Column9: Phone #
The problem is that the text file is rather choppy and disjointed. The customer records are not uniform in length. Some do not contain enough information for all 9 fields. Most records have more than 1 line of information when opened in Excel. There is, however, some uniformity: the indicator (if any) is always first, followed by last name and then first name. Also, an r: should always precede the address information. The following is a sample of the file:

+WALDON, Mrs. Diana (Diana A. Davies); 1977; r: 555 Hazel Dr.,
Warner Robins, PA 31088, 555 542-7811
+WALDRON, Edwin W.; 1985; Flight Attnd., United Airlines; r: 556
NW 24th St., Miami Shrs., FL 33150;
johndoe@aol.com
WALDRON, Heidi L. (Heidi L. Scheeringa); 1980; r: POB 1234,
Helen, PA 30545
WALDRON, Helen; 1935 (See Ruth, Mrs. Helen)
4-WALDRON, Minerva (Min); 1945; Retired Transcriber/Operator,
State of Florida; r: 557 S. K St., Lake Worth, FL 33460, 55712-
5183
WALENIUS, Mrs. Laura J. (Laura Jayne Doyle); 1961; r: 555
Ridge St., Lake Worth, FL 33460, 555 555-5873
WALENIUS, Richard S.; 1977; r: 5567 S. 9th Ave., Lake Worth,
FL 33461, 555 555-2726
WALKDEN, Arthur B.; 1965; r: 5568 Lake Ave., Lake Worth, FL
33460, 555 555-1889
WALKER, Cassondra; 2000; r: 5569 S. C St., Lake Worth, FL
33460, 555 555-6154
4-WALKER, Cecil; 1954; Retired; r 1234 Jog Rd, Lake Worth, FL
33467, 555 565-6660; Lois; Dale, Keith, Cynthia
WALKER, Charles L.; 1979; r: 123 S B St., Lake Worth, FL 33460
WALKER, David M.; 1969; r: 5570 Dunford Rd., Westlake, OH
44145
4-WALKER, Jeffrey; 1970; Admin., Univ. of Notre Dame; r: 55701
Ray Dr., Granger, IN 46530, 555 555-7541; Shari; Erin, Nathan,
Tim;
johndoe@nd.edu
+WALKER, Jeremy H.; 1991; Maintenance Worker II, Palm Beach
Cnty.; r: 55072 24th St. N, Royal Palm Bch., FL 33412, 555 555-
7311;
johndoe@juno.com
4-WALKER, Lillie (Lillie H. Robinson); 1979; Min ister/Ofc. Mgr.,
Christian Come Alive, 561 369-0950; r: 1234 Fairgreen Rd, W.
Palm Bch., FL 33417, 555 555-8337; Timonthy; Latoya, Laquin-
ton, Timonthy Jr



Please let me know if there are any Macros (or other options) that might help.
Any assistance will be greatly appreciated.
Thanks!

 

[Joe_Toz]

Yes, as the “non-conforming” record sample above shows, some records carry over to 2 (or more) rows sometimes and sometimes multiple records are combined (see WATERS, Tracy – WATFORD, Christopher in the sample).

 

[jindon]

Well, I think it is better to reconsider how to import the data rather than manupilate such unfinished data.

I should say "It is not even a form of data". It is just a block of string,

Tell your IT person to fix it and it makes you much easier.

 

[Joe_Toz]

I will take your advice and try to have it fixed, if possible. If it cannot be altered, I will make manual adjustments where needed. I want to once again thank you, Don, and everyone else who took the time to offer solutions to my problem. I sincerely appreciate it. You guys are the best!