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

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

On Tue, 22 May 2007 20:13:37 -0700, "Bruce" wrote:

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


One big problem in trying to do this is the

"etc..."

If you do not define your formats, you will need to program some kind of
Artificial Intelligence to determine that a format is unknown, and how to parse
it.

Also, in example 4, will the two LNames be the same?

Is LName always one word? If not, what are the possible variations?

It might be easier to coerce an intelligent data entry method, rather than
allowing a random method.

For the examples you post, it is relatively simple to define LName as the first
word which is followed by a comma, or a <space comma, or (if no commas) the
end of the line.

Using Longre's morefunc.xll add-in (free and downloadable from
http://xcell05.free.fr/

The regular expression formula:

=REGEX.MID(A1,"\w+(?=(\s?,)|$)")

will extract LName from the examples you give above.

But to do any more requires a complete definition of the various formats that
will have to be dealt with. As I wrote, much easier to coerce the proper
format -- perhaps using some kind of well defined input form.
--ron