Parsing data

  • Thread starter Thread starter bdonovan
  • Start date Start date
B

bdonovan

I need to parse out data in a name field into Salutation;
First; Last. The current data looks like this:

Dr. and Mrs. Adam Fuered

I want to parse it into separate fields.... any
suggestions?

Thanks.
 
Will the data ever have a middle name or initial...or a multi-word last or
first name, or multiple blank spaces between the three parts?

If not:

Dim lngLast As Long, lngFirst As Long
Dim LastName As String, FirstName As String, Salutation As String
lngLast = InStrRev([DataName], " ")
lngFirst = InStrRev([DataName], " ", lngLast - 1)
LastName = Mid([DataName], lngLast + 1)
FirstName = Mid([DataName], lngFirst + 1, lngLast - lngFirst - 1)
Salutation = Left([DataName, lngFirst - 1)
 
Sorry - in my first post, I forgot that you want to do this in a query.

Will the data ever have a middle name or initial...or a multi-word last or
first name, or multiple blank spaces between the three parts?

If not, you can do this in one of two ways:

Use calculated fields:
LastName: Mid([DataName], InStrRev([DataName], " ") + 1)

FirstName: Mid([DataName], InStrRev([DataName], " ", InStrRev([DataName], "
") - 1) + 1, InStrRev([DataName], " ") - InStrRev([DataName], " ",
InStrRev([DataName], " ") - 1) - 1)

Salutation = Left([DataName, InStrRev([DataName], " ", InStrRev([DataName],
" ") - 1) - 1)



or create two public functions in a regular module and then call them from
your query in calculated fields:

Public Function LocLastName(varData As Variant) As Long
LocLastName = InStrRev(varData, " ") + 1
End Function

Public Function LocFirstName(varData As Variant) As Long
LocFirstName = InStrRev(varData, " ", LocLastName(varData) - 2) + 1
End Function


Calculated fields in the query:
LastName: Mid([DataName], LocLastName([DataName]))

FirstName: Mid([DataName], LocFirstName([DataName]),
LocLastName([DataName]) - LocFirstName([DataName]) - 1)

Salutation = Left([DataName, LocFirstName([DataName]) - 1)
 
Back
Top