Parcing out Name field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a name field that contains the following format:

Last,First M Suffix

How can I split each portion out into its separate column (4 Columns):
Last
First
Middle Initial (if one is there)
Suffix (this can be anywhere from 2 to 10 characters)

Thank you for any advice and help that you can give me.
 
There are two ways I know of doing this. I am pretty sure there has to be an
easier way than what I am about to suggest, but I am not an expert.

You can select your records in the name field and paste them into notepad.
Save it as a csv-file. (extention .csv) Then open a fresh Excel document and
go to Data - Import external data and select your csv file. It will run
through a wizard to import the data and you should just say that you want
anything seperated by a comma or a space to be in a different column. I
haven't checked this myself, but i think it could work.
 
Thanks nick_81, I did consider approaching it that way, that way should work,
I will give it a shot.
 
Hi Chris

You don't need to go to the trouble of exporting and importing. There are
plenty of string manipulation functions in VBA which will do the job for
you.

Extracting the last name is easy because it's everything up to the comma.

Dim sFullName as string
Dim sLast as string
Dim sRest as String
Dim i as integer
' find the position of the comma
i = InStr( sFullName, "," )
sLast = Left( sFullName, i-1 )
sRest = Mid( sFullName, i+1 )

If the format of the rest of the string is completely consistent then it can
be split up in a similar way by finding the spaces. However, there may be
problems. Consider the following names:

1. Smith,Mary Jane Q
2. Smith,John Jnr
3. Smith,John Q

(1) has a double (but unhyphenated) first name, a middle initial, and no
suffix
(2) has a suffix but no middle initial
(3) has a middle initial but no suffix

No code will be clever enough to parse these correctly, as certain human
judgements need to be made.

What you could do is write a query to find the "problem" names, and edit
them to make them consistent. The following match criterion will find most
of them:
Not Like "*,* [A-Z] *"

You can then manually make temporary changes to them as required - for
example, change any spaces in first names to "@" and add a bogus middle
initial such as "%" to those names that don't have one.

Then you can split out the other three fields based on a space delimiter and
do a global replace later to remove your temporary characters.
 
Hi Graham,

What exactly does Not Like "*,* [A-Z] *" do? Thats the only part of your
explanation that I didn't get. Thanks.

Graham Mandeno said:
Hi Chris

You don't need to go to the trouble of exporting and importing. There are
plenty of string manipulation functions in VBA which will do the job for
you.

Extracting the last name is easy because it's everything up to the comma.

Dim sFullName as string
Dim sLast as string
Dim sRest as String
Dim i as integer
' find the position of the comma
i = InStr( sFullName, "," )
sLast = Left( sFullName, i-1 )
sRest = Mid( sFullName, i+1 )

If the format of the rest of the string is completely consistent then it can
be split up in a similar way by finding the spaces. However, there may be
problems. Consider the following names:

1. Smith,Mary Jane Q
2. Smith,John Jnr
3. Smith,John Q

(1) has a double (but unhyphenated) first name, a middle initial, and no
suffix
(2) has a suffix but no middle initial
(3) has a middle initial but no suffix

No code will be clever enough to parse these correctly, as certain human
judgements need to be made.

What you could do is write a query to find the "problem" names, and edit
them to make them consistent. The following match criterion will find most
of them:
Not Like "*,* [A-Z] *"

You can then manually make temporary changes to them as required - for
example, change any spaces in first names to "@" and add a bogus middle
initial such as "%" to those names that don't have one.

Then you can split out the other three fields based on a space delimiter and
do a global replace later to remove your temporary characters.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Chris said:
I have a name field that contains the following format:

Last,First M Suffix

How can I split each portion out into its separate column (4 Columns):
Last
First
Middle Initial (if one is there)
Suffix (this can be anywhere from 2 to 10 characters)

Thank you for any advice and help that you can give me.
Click to expand...
Click to expand...
 
nick_81 said:
Hi Graham,

What exactly does Not Like "*,* [A-Z] *" do? Thats the only part of your
explanation that I didn't get. Thanks.
Click to expand...

Hi Nick

Like is a wildcard match operator.
* is a wildcard for zero or more characters (any char)
[A-Z] is any single character in the range A-Z (i.e. one letter)

So the query will return any records that do NOT match the following:

something followed by a comma followed by something else, then a space,
then one letter, then a space, then something else

Actually, a better expression would be:

Not Like "[A-Z]*,[A-Z]* [A-Z] [A-Z]*"

Or, if the suffix can begin with something other than a letter, then replace
the last [A-Z] by a ?
 
Back
Top