Parsing Question

[william]

Hello,

I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:

Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George

I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.

I've successfully parsed the last name in a query with the following
statement:

Last: Left([Name],InStr([Name],",")-1) which produced the following:

Smith
Jones
Blackman-Pearson
Wright

So far so good.

I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:

First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:

Ellen P.
Mary Jane
Betsy D.
George

As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:

Ellen
Mary
Betsy
George

Any help is greatly appreciated.

Thanks

WPW

 

[Svetlana]

Mid(Mid([Name];Len(Left([Name];InStr([Name];",")+2)));1;IIf(InStr(1;Mid([Name];Len(Left([Name];InStr([Name];",")+2)));"
")=0;Len(Mid([Name];Len(Left([Name];InStr([Name];",")+2))));InStr(1;Mid([Name];Len(Left([Name];InStr([Name];",")+2)));"
")))

 

[Arvin Meyer [MVP]]

You've done it right. Just not gone far enough. You need to continue parsing
the first name column with one more:

Trim(Left([FirstName],InStr([FirstName]," ")))

 

[Albert D. Kallal]

Try:

LastName: split([MyName],",")(0)

FirstName:split(split([MyName],",")(1)," ")(0)

 

[Guest]

Hi William,

One of my favorite utilities for splitting names in Access is called
"Splitter for Microsoft Access". If you don't mind popping $39 for a copy,
you will have a very good utility that can untangle the messiest name data:

http://www.infoplan.com.au/splitter/

A very easy method to use involves starting over in Excel. Select the column
that includes the name data. Then use Data > Text to Columns... Choose
Delimited in the first wizard screen. Choose the space and comma as the
delimiters. Place a check in the option that reads "Treat consecutive
delimiters as one". Then re-import your table back into Access.

You can also create the new fields in the table and then use an update query
to populate the new fields. The update query would look like this:

Field: LastName
Table: Your tablename
Update To: SplitName([Name],Chr(44),0)

Field: FirstName
Table: Your tablename
Update To: SplitName([Name],Chr(32),1)

Create a new standard module. Then copy and paste the following function
into it. This will create a zero-based array named strResult. The On Error
Resume Next statement will prevent the function from choking to a halt if
there is no middle name for a given record.

Option Compare Database
Option Explicit

Public Function SplitName(strFullName As String, _
strCharCode As String, intElement As Integer) As String

On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], strCharCode)

SplitName = strResult(intElement)

End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hello,

I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:

Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George

I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.

I've successfully parsed the last name in a query with the following
statement:

Last: Left([Name],InStr([Name],",")-1) which produced the following:

Smith
Jones
Blackman-Pearson
Wright

So far so good.

I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:

First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:

Ellen P.
Mary Jane
Betsy D.
George

As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:

Ellen
Mary
Betsy
George

Any help is greatly appreciated.

Thanks

WPW