Name Parse Help Needed... 3

[JonWolgamuth]

I've read the excellent FAQ on name parsing, but I'm getting data that needs to be parsed that is in more than the 9 different formats that are listed.

Here are the formats I have to work with (all in one field)

FirstName MiddleName LastName Suffix
FirstName MiddleInitial LastName Suffix
FirstName MiddleInitial LastName
FirstName LastName (note extra spaces)

Sometimes the MiddleInitial has a (.) and sometimes it doesn't.

I need to parse this information into this format

LastName FirstName MiddleInitial Suffix

Isn't it great!!?? My first instinct was to tell the customer that we can't give them the data in the format that they desire. However, this is such a good group of folks that I thought I would give it a go here.

Thanks in advance!

 

[jebry]

Hi!

Try this function:

Public Function NameFormat(strName As String) As String

Dim strFirstName As String
Dim strLastName As String
Dim strMiddle As String
Dim strSuffix As String
Dim strReturnName As String
Dim lngFirstSpace As Long
Dim lngSecondSpack As Long

strReturnName = Trim(strName)
lngFirstSpace = InStr(strReturnName, " &quot
lngSecondSpace = InStr(lngFirstSpace, strReturnName, " &quot
strFirstName = Left(strReturnName, lngFirstSpace - 1)
If lngSecondSpace - lngFirstSpace = 1 Then
strLastName = Mid(strReturnName, lngSecondSpace + 1)
strMiddle = ""
strSuffix = ""
Else
strMiddle = Mid(strReturnName, lngSecondSpace - lngFirstSpace - 1)
lngFirstSpace = InStr(lngSecondSpace, strReturnName, " &quot
If lngFirstSpace = 0 Then
strLastName = Mid(strReturnName, lngSecondSpace + 1)
strSuffix = ""
Else
strLastName = Mid(strReturnName, lngFirstSpace - lngSecondSpace - 1)
strSuffix = Mid(strReturnName, lngFirstSpace + 1)
End If
End If

strReturnName = strLastName & " " & strFirstName
If strMiddle <> &quot;&quot; Then
strReturnName = strReturnName & &quot; &quot; & strMiddle
If strSuffix <> &quot;&quot; Then
strReturnName = strReturnName & &quot; &quot; & strSuffix
End If
End If

NameFormat = strReturnName

End Function

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

It looks do-able. How good are you with VB programming?

You need to count the number of spaces in your string to determine how many fields you have.

If the middle initial is blank will there always be an extra space between first and last name? If so this actually makes the code easier.

I have written a function that I call &quot;Field&quot; (I named it after a PICK Basic function and I sincerely hope that name doesn't conflict with anything. It's working so far). Basically you pass the function a string, a delimiter, section you want returned and # of sections to return and the function returns a specifc section of a delimited string.

Example: Given the string text = &quot;John T. Smith Jr.&quot;
FirstName = Field(text,&quot; &quot;,1,1)
FirstNameMI = Field(text,&quot; &quot;,1,2)
LastName = Field(text,&quot; &quot;,3) (the # of sections is optional, default is 1)

If you will always have an extra space when there is no MI then this function will return an emtpy string when called for the MI. If not, then you may need to get creative.

Here's the function. It's probably not the best written thing in the world, but it works. Paste it into a module in your database and I hope it helps you some.

Public Function Field(text As String, delimiter As String, section As Integer, Optional NumSections As Integer = 1) As String
    Dim pos As Integer
    Dim cntr As Integer
    Dim BeforeText As String, AfterText As String
    
    If IsNull(delimiter) Or delimiter = "" Then
        Field = text
        Exit Function
    End If
    
    'Start Loop to find beginning of section
    cntr = 1
    Do
        pos = InStr(text, delimiter)
        If pos = 0 Then
            If cntr = section Then
                Field = text
            Else
                Field = ""
            End If
            Exit Function
        End If
        BeforeText = Left(text, pos - 1)
        text = Right(text, Len(text) - pos)
        cntr = cntr + 1
    Loop Until cntr > section
    
    'Now need to find end of section
    cntr = 1
    Do Until cntr >= NumSections
        pos = InStr(text, delimiter)
        If pos = 0 Then
            Field = BeforeText
            Exit Function
        End If
        BeforeText = BeforeText & Left(text, pos - 1)
    Loop
    Field = BeforeText
End Function

Maq
<insert witty signature here>

 

[MichaelRed]

If you have 2K, there is function SPLIT which will do most of the work in a single step. If you do not have 2K, search these forums for basSplit, which will do almost all of what Split does. Given a string, it will &quot;split' it into 'words' according to your delimiter (Space). The remainder is just checking what is in the words and re-ordering them.

As an OBTW, while you are fiddling with the name game, you MIGHT want to create additional fields in the table and retain the fragments as individual fields for future specialized formatting.

And - One MORE caution. After the names are split / re-arranged, you really do need a manual verification of each record/field. No matter HOW hard you try, if you do the name game on anything but the trivial recordset, the programatic process WILL mis-translate some items.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Maquis]

Oooh, I really wish Access 97 had split. It would sure come in handy. Maq
<insert witty signature here>

 

[MichaelRed]

Oooh, I really wish Access 97 had split. It would sure come in handy.

Maq
<insert witty signature here>
<wish I had read the whole response here>



If you do not have 2K, search these forums for basSplit MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over