Can This Be Done With A Macro?

[Josh in Tampa]

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-
4538","","(e-mail address removed)"

JOAN AMLOE," 2-23845","2436 CIDARCREST
PLACE","VALRICO/FL","33594","(813)661-
4677","(e-mail address removed)",""

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

 

[Josh in Tampa]

your thoughts on how to separate city, st???????

i'm trying to figure out the best way to separate the city
and state that exist in one cell and separate them into
two cells.

examples:

Tampa, FL
St. Petersburg, FL
Tampa/FL
Tampa-FL
Saint Petersburg, FL
Duluth,,GA
Ruskin-FL, FL
Ocala , FL
Beaver Dam, NB, Canada

unfortunately, i have no control over how this information
is input. if i did, i could eliminate alot of the
headaches associated with separating the city and state.

i'm thinking that maybe i need so search for a comma. if
i find one then i know that everything to the left of the
comma is the city....unless of course i'm dealing with
Beaver Dam, NB, Canada. i suppose in the case of Beaver
Dam......i could just continue searching for commas until
there are no commas found. then everything to the left
would be the city.

then i could conduct the same search, but for dashes and
backslashes, etc.

problem arises when i think about entries that have no
comma, backslash, or dash.....just spaces.

Tampa FL
New Port Richey FL

hmmmmmmm. how can i identify the last instance of a space
in a string so that i can make everything to the left of
it the city? what if someone inputs something like Beaver
Dam NB Canada? then i'm screwed. there has to be a way.
hmmmmmm.

maybe i can create a list of states, state abbreviations,
countries, country abbreviations, that i could use to
match up my strings against. if i found an instance of
one, i could then take everything to the left.

your thoughts??

 

[Guest]

more thoughts......

noticing now as i examine the comma-delimited list that i
start with initially........that there are an abundance of
little errors like there not being enough empty strings
inserted inbetween two particular fields which leads to
entries being inserted into the wrong column/cell.

EXAMPLE:

"Jane Doe","","","33534",""

should be:

"Jane Doe","","","","33534"

this kind of an error leads to entries being in the wrong
place.

so, maybe i need to manipulate the comma-delimited list
BEFORE i run TEXT TO COLUMNS. do you imagine that might
be the right idea?

 

[Ed]

The easy answer is - yes, of course it can! Now for the hard part: how?!?

After reading all your thoughts, you *do* have a can of worms. You didn't
say how many of these are on your list, but I suspect that if you could
easliy scan down the list a fix them, you wouldn't be looking for a code
solution. Unfortunately, until you get your list in a shape to be dealt
with, it's going to take a lot of "user-intervention" - you, the user,
telling the program what to do with this cell's data.

One suggestion for building this is to deal with one issue at a time in a
single macro, then call each one in a "master macro. This is rather clunky,
I know, and requires passing variables across the macros, but it save you
from going insane trying to keep loops and IFs separate from each other.

For instance, separating the names has been dealt with on the newsgroups
before. A Google groups search for "last first name separate group:*Excel*"
yielded 512 responses. (If you don't have Ron DeBruin's excellent Google
search tool, I suggest you get it from http://www.rondebruin.nl/Google.htm -
it makes life so much easier!) You can then decide what do do with the
results of what you get - some of it you may be able to program ("if this is
a single letter, it's probably a middle initial only - delete the cell and
shift the others over"), and some you may have to program "show me anything
that doesn't match this criteria so I can deal with it."

Likewise the addresses. Find a list of all the US state abbreviations and
put them in a column. Tell your macro to find any cell with a match to one
of those strings and copy to another column (maybe even another worksheet).
Do the same for common country names and abbreviations. Then sort that list
and you can scan it for things out of place. If it's a reoccurring
out-of-place, then you can program "if you see this, do that". Otherwise,
it's "user-intervention" time.

Bottom line - it's do-able, but probably not by the end of the day. Another
thought - would your company be better served by giving these people an
input form with data validation restrictions? That would make it very easy
to populate a database and create reports.

HTH
Ed