Name Parsing

[tong]

I have a field that contains lastname MiddleName and
firstname as one field, for example:
Smith, Joe, Robert
Klein, Kathy
Hoffman
Chan, Mary, Julie
Hoffa, Julie

I want to parse out the name field to:
[LastName]
[MiddleName]
[FirstName]

As the example shows, the name field may not have the
full name. The problem, I have in parsing is when the
field only has a last name.

 

[John Nurick]

Hi Tong,

The condition
InStr([NameField],",")=0
is true when the NameField doesn't contain a comma and therefore
contains only the last name.

Parsing data out of a field this complicated is IMO easier to do by
building custom VBA functions, one for each value you're parsing out,
than by using complicated nested expressions. Something like this air
code:

Public Function GetLastName(V As Variant) As Variant
Dim strS as String
Dim lngPos1 as Long

If IsNull(V) Then
GetLastName = Null
Exit Function
End If
strS = CStr(V)
lngPos1 = InStr(strS, ",")
If lngPos1 = 0 Then
GetLastName = strS
Else
GetLastName = Left(strS, lngPos1 - 1)
End If
End Function

I have a field that contains lastname MiddleName and
firstname as one field, for example:
Smith, Joe, Robert
Klein, Kathy
Hoffman
Chan, Mary, Julie
Hoffa, Julie

I want to parse out the name field to:
[LastName]
[MiddleName]
[FirstName]

As the example shows, the name field may not have the
full name. The problem, I have in parsing is when the
field only has a last name.

 

[David Hove]

I find it easier to copy the entire field from its table
into a blank Excel column, make lots of copies for
safety, then , using one of these copies do a search
for "*,*" and replace all instances with "*," which will
leave you with just the last names.

Then remove the surname part from the total etc etc.

It takes time but it works