ParsingName
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
.
|