I'm sure there is an easier way, but there is a mid() function that
will allow you to selectively pull from the middle of the field.  If
the ampersands are consistent I'm sure this will work to get
everything out.  Good luck!

You have a real problem. You can easily get the last name and the zip and
the state, but the rest is problematic.. Are the cities Chicago or St
Chicago (as in St Louis) Madison or Drive Madison? What if there is no Mary
or Cindy? No matter how you do it, you're going to have a lot of manual
work. Even if you split the data into columns (space delimiter) the result
will be inconsistant. Can you get the original file, before it was put into
Excel?

Hi Tom,

This may help you get some of the way.
Do Data>Text to Columns using comma as the delimeter.

Then on your second column do an Edit>Replace for each digit.
Find what:-       space1
Replace with:-    ~1
Then repeat with
Find what:-       space2
Replace with:-    ~2
etc
...........
...........
Down to
Find what:-       space9
Replace with:-    ~9

You can then do a further Text to columns on the ~
or use it as a Find character.

Still a bit of manual work but hopefully that will
help you get part way.

HTH
Martin

Tom,

This is a tough one.  Your data is quite ambiguous as to names, addresses, city, etc.  As
Tyro said, you'll probably have some manual work.  One workable, if manual, way is to put
the stuff in a Word table.  Set up columns as you need (city, state, etc).  Then you can
quickly select parts and drag them to the column where they belong.  Manual, but fast.
Several thousand records???  Ick.  Hire someone.  If you use one any of the parsing
techniques to do the parsing automatically, you can still use Word for cleanup, as required.
You can easily put it in Word initially with copy/paste, or by opening the Excel file
directly in Word.  DON'T SAVE IT IN WORD, or it'll clobber your original Excel file.

Rob Bovey had a pretty sophisticated Excel parser for this kind of stuff.  It was many years
ago.  See if it's available at his web site, www.appspro.com.

If your layout is always in the same format, and in particular if there are
only single first names, then this can be done using Regular Expressions.

The main problem is to differentiate the first name(s) from the beginning of
the address.  The rule that I used in the example below is this pattern:

<comma><space>
<word>
then, optionally:
    <space>
    ampersand (&)
    <space>
    <word>

But this will fail if you have a "double" first name.  For example:

Smith, Mary Jane 47 Edgecomb rd. ...

If that is the case, we would need to come up with a different rule.  But try
this, anyway, and see if it works.

If your addresses always start with a number, we could incorporate that as a
rule and get around the issue of double first names.

Also, you need to list your cities by name.  In the Pattern I devised, I listed
Chicago and Madison.  You can add more possibilities by extending the
pipe-delimited list in Pattern.

First, enter the UDF into your workbook:

    <alt-F11> opens the VB Editor
    Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

The following formulas should work:

Last Name:      =remid(A1,"^[^,]*")
First Name(s):    =remid(A1,Pattern,1)
Street Address:    =remid(A1,Pattern,3)
City:        =remid(A1,Pattern,4)
State:        =remid(A1,Pattern,5)
Zip:        =remid(A1,Pattern,6)

Pattern:
,\s+(\w+(\s+&\s+\w+)?)\s+(.*)\s+(Chicago|Madison),\s+([A-Z]{2})\s+(\d{5}(-\d{4})?)

=============================================
Option Explicit
Function reMid(str As String, spattern As String, Optional Index) As String
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.ignorecase = True
re.Pattern = spattern

If re.test(str) = True Then
   Set mc = re.Execute(str)
   If IsMissing(Index) Then
       reMid = mc(0)
   Else
       reMid = mc(0).submatches(Index - 1)
   End If
End If
End Function
=============================================

This should handle any entries that are in the pattern you presented.  Examples
that don't work would be useful in trying to refine this routine.
--ron

Guys:

Thank you all for the many excellent suggestions. I'm grateful that so many
people would try to help out. What a great community!

After carefully scanning thorugh the list, it looks like there are two
instances where the street address does not start with a number (darn) --  
those address that start PO Box XXX, or RR# XXX. Most of the first names are
of the format "Spouse 1 & Spouse 2" or just "Spouse 1".

Let me work throught the suggestions from Ron, Martin, and Earl and see if I
have any success. Again many thanks.

Tom K

P.S. What does "OP" mean?