okay....i just sat down at my desk. it's 8 AM, and i
really want to complete this automation project by the
day's end. so, i was thinking it might be a good idea if
i provide a detailed summary of what i need to
accomplish. any help will be greatly appreciated.

alright......here goes:

i work for an organization that sells tickets. lots of
them. the sales force relies heavily on leads they
receive from various sources to make sales calls. one of
my major responsibilities is managing the information that
is submitted to the sales staff that comes from the
various sources.......in other words, i take ugly comma-
delimited lists and i import them into excel and clean
them up nice and pretty.

i need to automate this process. follow me now.....

i receive via email a text file....the comma-delimited
list that i mentioned previously. i import this text file
into a new excel worksheet. so, my worksheet is populated
with a long list of information that needs to be cleaned
up.

my first step is DATA -- TEXT TO COLUMNS. a window pops
up and i have to choose how to separate the text.....fixed
width or delimiter. i choose delimiter and designate the
delimiter to be a comma. i click OK, and the comma-
delimited list is now broken up into several columns and
rows of data.

after TEXT TO COLUMNS, the columns include, from left to
right (without headers):

A = Full Name
B = Sales Code
C = Address
D = City, State
E = Zip
F = Phone
G = Blank ?????????
H = EMAIL

column G is empty except for a handful of EMAIL
addresses......and where G is populated with an EMAIL
address, column H is empty. so, it's clear to see after a
little investigation, that from time to time the person
creating the comma-delimited list makes an error.

here is a snippet of some of the comma-delimited list:

VICTORIA ALLEN," 2-23340","HILL HOUSE","HATFIELD
PEVEREL,,GB","CM32ET","(004)412-
"

JOAN AMLOE," 2-23845","2436 CIDARCREST
PLACE","VALRICO/FL","33594","(813)661-
",""

you will notice that in the first instance there is an
empty string inserted just in front of the email
address.....which is why there is an empty G column and a
populated H column.......in the second instance you will
find that there is no empty string inserted, and so of
course there is a populated G column and an empty H column.

(i know this seems long-winded, but i want to provide
anyone willing to help out with as much detail as
possible.)

okay, so we have the columns.....A thru H.

A (full names).....needs to be trimmed and proper. easy
enough: trim(proper(a1)).

B (sales codes).....needs to be deleted.

C (addresses)......needs to be trimmed and proper.

D (city, state).....needs to be separated. one column for
city, one column for state. i'll need to create a user-
defined function to handle this because there are often
errors in this particular cell (ie., TAMPA-FL FL, or
TAMPA/FL, or TAMPA FL or TAMPA,FL, and so on and so
forth). it's just a pain using the TEXT TO COLUMNS
repeately in an attempt to finally separate the city from
the state. ultimately, the city needs to be trimmed and
proper. one of the issues here is the fact that sometimes
when i run the TEXT TO COLUMNS on this particular column,
there are more than one new column created......like when
the existing entry is TAMPA FL FL....a bonehead error, but
it happens....and when it does, the result is two
additional columns instead of just one for the state.

E (zip code).....needs to be truncated after 5 digits.

F (phone number).....nothing required here.

G and H (email).....here, existing G records need to be
inserted into H. after that's done, G needs to be deleted.

WHEW!!!! what a mouthful!! i don't know if anyone out
there will be willing to help with such a comprehensive
list of issues, but i feel better having put this out
there for you all to examine.

any help will be greatly appreciated!!!!! thanks in
advance!!!!!


josh in tampa