Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParsingName
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|