Parsing inconsistent data

[Alan B. Densky]

I'm using Access 2000.

I have a names field that has inconsistent data in it. Sometimes it only has
a
FirstName and a LastName, and sometimes it also has a middle initial with or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName by taking the
first name
from the left, and the last name from the right.

FirstName: Trim(Left([CustomerName],InStr([CustomerName]," ")))
LastName: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

I'd like to know how I can parse out the middle initial, or the middle
initial and the period
if it exists.
Thanks,

Alan

 

[Jeff Boyce]

Alan

One approach would be to do this as a multi-step process...

If you take your initial string and search for the first space, then take
the "remainder" after that first space, if what you have left (still) has a
space in it (InStr()), you have a middle name/initial.

Or, you could have, as I ran across the other day, someone with two last
names, NOT hyphenated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Names are TOUGH to parse if they are combined. See the following samples

John P Spencer, Jr
Oscar de la Rente
David L Van Derloo
Mary Anne L Brown

That said if the names are always one, two, or three words and you are
willing to live with word one being the first name and the last word being
the last name and the 2nd word being the middle name if there are three
words, then you can do this all in one query.

You already have First Name and Last Name figured out, so all you need is to
figure out how to get middle name. The following should return everything
that isn't the first word and isn't the last word.

IIF(Len([CustomerName)- Len(Replace([Customer Name]," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," ")))
)


Given the sample above you would get the following returned as the middle
P Spencer, <<< note the comma
de la
L Van
Anne L

Good luck
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alan

One approach would be to do this as a multi-step process...

If you take your initial string and search for the first space, then take
the "remainder" after that first space, if what you have left (still) has
a space in it (InStr()), you have a middle name/initial.

Or, you could have, as I ran across the other day, someone with two last
names, NOT hyphenated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2000.

I have a names field that has inconsistent data in it. Sometimes it only
has
a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName by taking the
first name
from the left, and the last name from the right.

FirstName: Trim(Left([CustomerName],InStr([CustomerName]," ")))
LastName: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

I'd like to know how I can parse out the middle initial, or the middle
initial and the period
if it exists.
Thanks,

Alan

 

[Alan B. Densky]

Thanks John, I'll give that a shot!

Alan

Names are TOUGH to parse if they are combined. See the following samples

John P Spencer, Jr
Oscar de la Rente
David L Van Derloo
Mary Anne L Brown

That said if the names are always one, two, or three words and you are
willing to live with word one being the first name and the last word being
the last name and the 2nd word being the middle name if there are three
words, then you can do this all in one query.

You already have First Name and Last Name figured out, so all you need is to
figure out how to get middle name. The following should return everything
that isn't the first word and isn't the last word.

IIF(Len([CustomerName)- Len(Replace([Customer Name]," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," ")))
)


Given the sample above you would get the following returned as the middle
P Spencer, <<< note the comma
de la
L Van
Anne L

Good luck
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Alan

One approach would be to do this as a multi-step process...

If you take your initial string and search for the first space, then take
the "remainder" after that first space, if what you have left (still) has
a space in it (InStr()), you have a middle name/initial.

Or, you could have, as I ran across the other day, someone with two last
names, NOT hyphenated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2000.

I have a names field that has inconsistent data in it. Sometimes it only
has
a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName by taking the
first name
from the left, and the last name from the right.

FirstName: Trim(Left([CustomerName],InStr([CustomerName]," ")))
LastName: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

I'd like to know how I can parse out the middle initial, or the middle
initial and the period
if it exists.
Thanks,

Alan

 

[Alan B. Densky]

John,

I tried it but I'm getting an error message that it has the wrong number of
arguments. (Yes I did include the last ) that is wrapped to the last line.

Alan

Names are TOUGH to parse if they are combined. See the following samples

John P Spencer, Jr
Oscar de la Rente
David L Van Derloo
Mary Anne L Brown

That said if the names are always one, two, or three words and you are
willing to live with word one being the first name and the last word being
the last name and the 2nd word being the middle name if there are three
words, then you can do this all in one query.

You already have First Name and Last Name figured out, so all you need is to
figure out how to get middle name. The following should return everything
that isn't the first word and isn't the last word.

IIF(Len([CustomerName)- Len(Replace([Customer Name]," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," ")))
)


Given the sample above you would get the following returned as the middle
P Spencer, <<< note the comma
de la
L Van
Anne L

Good luck
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Alan

One approach would be to do this as a multi-step process...

If you take your initial string and search for the first space, then take
the "remainder" after that first space, if what you have left (still) has
a space in it (InStr()), you have a middle name/initial.

Or, you could have, as I ran across the other day, someone with two last
names, NOT hyphenated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2000.

I have a names field that has inconsistent data in it. Sometimes it only
has
a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName by taking the
first name
from the left, and the last name from the right.

FirstName: Trim(Left([CustomerName],InStr([CustomerName]," ")))
LastName: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

I'd like to know how I can parse out the middle initial, or the middle
initial and the period
if it exists.
Thanks,

Alan

 

[John Spencer]

Let me try to remove my typos

IIF(Len(CustomerName)- Len(Replace(CustomerName," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," "))))

I had a space in CustomerName and had one bracket in front of customerName.
If your field has spaces in it then you will have to add the brackets in.
Hopefully the above has all the parentheses balanced correctly and they are
all in the right place. I tested this in the immediate window and it seems
to work.

IIF(Len([CustomerName])- Len(Replace([CustomerName]," ","")) <2, Null,
Trim(Mid([CustomerName], Instr(1,[CustomerName]," ")+1,
InstrRev([CustomerName]," ") - Instr(1,[CustomerName]," "))))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

I tried it but I'm getting an error message that it has the wrong number
of
arguments. (Yes I did include the last ) that is wrapped to the last line.

Alan

Names are TOUGH to parse if they are combined. See the following samples

John P Spencer, Jr
Oscar de la Rente
David L Van Derloo
Mary Anne L Brown

That said if the names are always one, two, or three words and you are
willing to live with word one being the first name and the last word
being
the last name and the 2nd word being the middle name if there are three
words, then you can do this all in one query.

You already have First Name and Last Name figured out, so all you need is to
figure out how to get middle name. The following should return
everything
that isn't the first word and isn't the last word.

IIF(Len([CustomerName)- Len(Replace([Customer Name]," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," ")))
)


Given the sample above you would get the following returned as the middle
P Spencer, <<< note the comma
de la
L Van
Anne L

Good luck
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Alan

One approach would be to do this as a multi-step process...

If you take your initial string and search for the first space, then take
the "remainder" after that first space, if what you have left (still) has
a space in it (InStr()), you have a middle name/initial.

Or, you could have, as I ran across the other day, someone with two
last
names, NOT hyphenated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2000.

I have a names field that has inconsistent data in it. Sometimes it only
has
a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName by taking the
first name
from the left, and the last name from the right.

FirstName: Trim(Left([CustomerName],InStr([CustomerName]," ")))
LastName: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

I'd like to know how I can parse out the middle initial, or the middle
initial and the period
if it exists.
Thanks,

Alan

 

[Alan B. Densky]

Thanks John, That works great!!

Alan

Let me try to remove my typos

IIF(Len(CustomerName)- Len(Replace(CustomerName," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," "))))

I had a space in CustomerName and had one bracket in front of customerName.
If your field has spaces in it then you will have to add the brackets in.
Hopefully the above has all the parentheses balanced correctly and they are
all in the right place. I tested this in the immediate window and it seems
to work.

IIF(Len([CustomerName])- Len(Replace([CustomerName]," ","")) <2, Null,
Trim(Mid([CustomerName], Instr(1,[CustomerName]," ")+1,
InstrRev([CustomerName]," ") - Instr(1,[CustomerName]," "))))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John,

I tried it but I'm getting an error message that it has the wrong number
of
arguments. (Yes I did include the last ) that is wrapped to the last line.

Alan

Names are TOUGH to parse if they are combined. See the following samples

John P Spencer, Jr
Oscar de la Rente
David L Van Derloo
Mary Anne L Brown

That said if the names are always one, two, or three words and you are
willing to live with word one being the first name and the last word
being
the last name and the 2nd word being the middle name if there are three
words, then you can do this all in one query.

You already have First Name and Last Name figured out, so all you need

is
to

figure out how to get middle name. The following should return
everything
that isn't the first word and isn't the last word.

IIF(Len([CustomerName)- Len(Replace([Customer Name]," ","")) <2, Null,
Trim(Mid(CustomerName, Instr(1,CustomerName," ")+1,
InstrRev(CustomerName," ") - Instr(1,CustomerName," ")))
)


Given the sample above you would get the following returned as the middle
P Spencer, <<< note the comma
de la
L Van
Anne L

Good luck
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Alan

One approach would be to do this as a multi-step process...

If you take your initial string and search for the first space, then take
the "remainder" after that first space, if what you have left (still) has
a space in it (InStr()), you have a middle name/initial.

Or, you could have, as I ran across the other day, someone with two
last
names, NOT hyphenated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2000.

I have a names field that has inconsistent data in it. Sometimes it only
has
a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName by

taking
the

first name
from the left, and the last name from the right.

FirstName: Trim(Left([CustomerName],InStr([CustomerName]," ")))
LastName: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

I'd like to know how I can parse out the middle initial, or the middle
initial and the period
if it exists.
Thanks,

Alan