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

[lesperancer]

use right(first,1) = "." to identify those with initials
and then use another inStr() function to get just the first name

william wrote:

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

[william]

Thank you. That definitely identifies those with initials, but what
about those with the first and middle name written out (e.g. Mary
Jane)?

William

On Nov 4, 9:42 am, lesperan...@nat pro.com wrote:

use right(first,1) = "." to identify those with initials
and then use another inStr() function to get just the first name

william wrote:

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

[fredg]

On 4 Nov 2006 05:34:40 -0800, william wrote:

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

Two things for you to consider.
1) Your first sentence formatted as Last, First, MI. < indicates
commas separating each of the names, but your examples

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

show commas only after the Last Name, and a space separating the First
and middle names/initials.

Which is it, a comma or a space?

Paste the below code into a Module:
Public Function ParseMixed(Text In As String, SplitChar As String, x)
As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, SplitChar, -1)
ParseMixed = Var(x)
End Function
=============== ===

In a query to separate the LastName:

LastName:ParseM ixed([CombinedNames],",",0)

Then if the first names or initials are separated by a comma, i.e.
Smith, Ellen, P in a query:

FirstName:Exp: ParseMixed([CombinedNames],",",1)

However if the FirstNames are separated from each other by a space,
i.e. Smith, Ellen P. then use:

FirstName:Exp: ParseMixed([CombinedNames]," ",1)

2) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[william]

Thanks Fred and sorry about the confusion. The latter was correct. A
comma appears only after the Last Name, and a space separates the First
and middle names/initials.

Your solution worked perfectly.

Thank you for your help.

William
fredg wrote:

On 4 Nov 2006 05:34:40 -0800, william wrote:

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

Two things for you to consider.
1) Your first sentence formatted as Last, First, MI. < indicates
commas separating each of the names, but your examples

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

show commas only after the Last Name, and a space separating the First
and middle names/initials.

Which is it, a comma or a space?

Paste the below code into a Module:
Public Function ParseMixed(Text In As String, SplitChar As String, x)
As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, SplitChar, -1)
ParseMixed = Var(x)
End Function
=============== ===

In a query to separate the LastName:

LastName:ParseM ixed([CombinedNames],",",0)

Then if the first names or initials are separated by a comma, i.e.
Smith, Ellen, P in a query:

FirstName:Exp: ParseMixed([CombinedNames],",",1)

However if the FirstNames are separated from each other by a space,
i.e. Smith, Ellen P. then use:

FirstName:Exp: ParseMixed([CombinedNames]," ",1)

2) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail