Bruce,
You have couple of options:
1 - Beat the various sources of data with a large stick until conformity is
achieved.
2 - Refuse the assignment, citing GIGO; Garbage In, Garbage Out.
3 - If all the data in each file/WS is only in one format, that you can
easily tell but looking, write series of functions/classes that each deal
with the separation of a format
4 - If the data is thoroughly mixed in a data file, resort to #2, or process
each line according to #3
5 - If you feel it worthwhile, write some code to guess/evaluate which
format each WS/line is in, then resort to #3 or 4.

Maybe this will give you some ideas. You should add error handling and
tidying up, e.g. TRIM etc
You could make the code more generic by first removing extra spaces,
assuming "&" cannot be part of someone's name:
REPLACE: " AND " "&"
REPLACE: " & " "&"
...Split and order code
REPLACE: "&" " & "


Private Sub CommandButton1_Click()
Process_FirstAMPSpouseSPACELName Range("A1:A40")
End Sub

Private Sub CommandButton2_Click()
Process_LNameCOMMAFirstANDSpouse Range("A1:A40")
End Sub

'e.g. Mary & John Johansson
Public Function Process_FirstAMPSpouseSPACELName(DataRange As Range) As Long

With DataRange
'Fix up the " & " for "&"
.Replace What:=" & ", Replacement:="&", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

'Split the sections
.TextToColumns Destination:=DataRange, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo
_
:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True

'Return the "&" to " & "
.Replace What:="&", Replacement:=" & ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End With

End Function


'e.g. Johansson, Mary AND John
Public Function Process_LNameCOMMAFirstANDSpouse(DataRange As Range) As Long

With DataRange
'Fix up the "AND" for "&"
.Replace What:=" AND ", Replacement:=" & ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

'Split the sections
.TextToColumns Destination:=DataRange, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True

'Switch the columns
.Copy .Offset(0, 2)

'Delete the original
.Delete xlToLeft

End With

End Function

'....etc

NickHK

"Bruce" wrote in message
...
I get assignments with the name in various formats.
First & Spouse LName
LName, First AND Spouse
Lname, First
First LName , Spouse Lname
First LName
etc....

I am trying to figure out a way to get First (and spouse seperated with a
"
& " ) in one cell and then the lastename in the next cell.
How could I do this?

Thanks
Bruce