Help with bad mail file

[SteveL]

Here is my situation... We have received a file we are
to use as a mailing file. Here is what it look like:

John Doe
Director, Public Relations
Some Corp.
331 South West Rd.
Somecity, IL 61114

Public Relations Director
Some Other Corp.
800 Nowhere St.
Anywhere, IL 66601

Jane Doe
Director, Marketing
Her COmpany
990 Wonderful Dr.
Madison, WI 65656

What I need to have happen is to put the respective
fields into a proper tab delimited file like this...

John Doe (tab) Director, Public Relations (tab) Some
Corp. (tab) 331 South West Rd. (tab) Somecity (tab) IL
(tab) 61114 (hard return)
Public Relations Director (tab) Some Other Corp. (tab)
800 Nowhere St. (tab) Anywhere (tab) IL (tab) 66601 (hard
return)
Jane Doe (tab) Director, Marketing (tab) Her Company
(tab) 990 Wonderful Dr. (tab) Madison (tab) WI (tab)
65656 (hard return)

I can do most of this by recording a macro with find and
replaces but the real issues are three:

1) I need the City, State, and Zip separated with tabs.
2) Remove the comma after the city.
3) In the case of the second example, since there is no
name for the first field, make it blank so the other
fields are in the right place.

HELP!!!

 

[Tom Ogilvy]

For maximum control you can use low level file io

http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications

I don't see the role for find and replace in your scenario, unless you mean
the replace vba function. The first two questions are parsing problems
which you should be able to accomplish with some if statements and some
string handling. The last would require your software to be able to read
and differentiate the difference between a name and a title unless all your
addresses with names are five lines and all your addresses without names are
4 lines.