It's coffee time.
These turn into manual efforts. I think my favorite way to attack this problem
is to separate each word/number into its own column (delimited by spaces).
Then insert a new column A and put this formula in:
=counta(b2:iv2)
(and drag down--headers in row 1???)
Now I'd sort by that helper column and start rebuilding your fields.
Insert new columns B:whatever.
And as long as you're doing it, put the city in one column, state in another,
zip in another--make it as granular as you can (and have the fields make sense).
Then (if you're lucky), you can copy the formulas down for each group of rows
with the same number of elements.
When you get more fields, you'll have to fix your formulas and then copy them
down.
There will be lots that don't fit--but you'll be able to get lots corrected
pretty fast.
And add a column that says whether your new stuff is good or not.
Then you can apply data|filter|autofilter and show just the bad ones and look
for patterns to group and fix as groups.
If you're building the company name, it could look like:
=a2&" "&b2&C2
if there were 3 components to the name.
Remember to save often--I'd save as a new workbook every so often--so when you
screw it up (and you will!), you can just go back to that previously saved
workbook:
myinputfile_2004_11_08_10_15_35.xls
If I saved it on Nov 8, 2004 at 10:15:35 am.
It's a lot of work, and once you've got it cleaned up, don't let anyone just
type in the data--make them use the correct fields!
