I am using Access 97. There is a column in the spreadsheet that has
names in it Smith. Jeff Sr A the last name is followed by a period. I
want to copy the Lastname , without the ., firtsname, and middle initial
, with a . to seperate columns.

Col 4 Col5 Col6

Smith Jeff A.

Is there a function avaliable for this?


TIA

You can use a combination of the FIND() function to get
the full stop and the LEFT() function to get surname.

A UDF ( User Defined Function) would look like this:

Public Function SplitName(sText As String) As String()

Dim results(1 To 3) As String
Dim pos As Long
Dim temp As String
pos = InStr(sText, ".")

If pos = 0 Then
results(1) = sText

Else
results(1) = Left(sText, pos - 1)
temp = Trim(Mid(sText, pos + 1))
pos = InStr(temp, " ")
If pos 0 Then
results(2) = Mid(temp, pos + 1)
results(3) = Left(temp, pos - 1)
Else
results(3) = temp
End If
End If

SplitName = results

End Function

This is a function that returns an array, so you need to
enter it as an array formula on the worksheet.
eaxmple
in E6 is the value
Smith. Jeff Sr A

Select three cells G6:I6
In the formula box type
=SplitName(E6)
hold down the ctrl + shift keys then press enter
the results are
G6:= Smith
H6:= Sr A
I6:= Jeff


HTH
Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I am using Access 97. There is a column in the
spreadsheet that has
names in it Smith. Jeff Sr A the last name is followed
by a period. I
want to copy the Lastname , without the ., firtsname,
and middle initial
, with a . to seperate columns.

Col 4 Col5 Col6

Smith Jeff A.

Is there a function avaliable for this?


TIA

.