How can I parse this kind of data...?

[Kelvin Beaton]

I have a field with names in it and I would like to get the first and last
names, but the data looks like this.

Jim Bob Brown
Jim Billy Bob Brown
Jim B Brown

If I can assume that the first name is Jim and the last name is brown I
could live with that.
I don't really need the middle part(s).
The names are separated by space.
I'm using Access 2003.

Any great ideas on how to parse this date into first and last names?

Thanks

Kelvin

 

[Klatuu]

But you can't assume the last part is the last name or the first part is the
first name.
Jim Billy Bob Brown, Jr.
Jim Billy Bob Brown MD
Jim Billy Bob Brown III
Jim Billy Bob Brown Esq.
Jim Billy Bob Brown, Phd.
Dr. Jim Billy Bob Brown
Sir Jim Billy Bob Brown
Hon Jim Billy Bob Brown

The difficulty of parsing names is exceeded only by the difficulty of
parsing addresses. In a perfect world, each part is carried in a different
field and concantenated, where appropriate, for display purposes. In lieu of
a perfect world, you will need to write a function to evaluate the parts and
determine if it is a name prefix or suffix and omit those that are.
I would start with the Split function.

Dim aryParts as Variant

aryNameParts = Split(strFullName, Space(1))

Will return an array of the parts. Then you can loop through them and pars

 

[John W. Vinson]

I have a field with names in it and I would like to get the first and last
names, but the data looks like this.

Jim Bob Brown
Jim Billy Bob Brown
Jim B Brown

If I can assume that the first name is Jim and the last name is brown I
could live with that.

But you can't.

My friend Darla Sue Jones (well, that's not her real last name) uses Darla Sue
as her first name. It's not Darla, it's Darla Sue.

My former coworker Felix de la Iglesia's last name is de la Iglesia. It's not
Iglesia, and he would quite rightly object if addressed in that way.

Parsing names requires a USB interface - Using Someone's Brain!

John W. Vinson [MVP]

 

[John Spencer]

You can use Instr to find the first space and InStrRev to find the last
space. And with that information you should be able to get the First word
and the Last word

LEFT(SomeField, Instr(1,SomeField," ")-1)
RIGHT(SomeField,InStrRev(SomeField," ") +1)

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

 

[Klatuu]

That explains a lot.
My USB only operates at 1200 baud with a 64K memory chip. And, the memory
is volitle. Every time I unplug it, it looses its data.
I have been told it is too old to upgrade, I need a replacement.

 

[Kelvin Beaton]

I'm getting the idea that USB will be required.... ;-)

Thanks

Kelvin

 

[John W. Vinson]

That explains a lot.
My USB only operates at 1200 baud with a 64K memory chip. And, the memory
is volitle. Every time I unplug it, it looses its data.
I have been told it is too old to upgrade, I need a replacement.

<SNORK!!!>

Well, the good news is that the add-in chip should be out soon.

The bad news is that it runs on unpatched Windows Vista...

John W. Vinson [MVP]

 

[j.mapson.nurick]

This can reduce but not eliminate the amount of USB-work needed:
http://www.infoplan.com.au/splitter/

 

[Kirstie Adam]

Hi Kelvin,

I recently had to do something similar for a database (created by another
member of staff) that had the whole name in one field, and we wanted
FirstName and SurName fields.

I exported the relevant table into Excel, then used DATA - TEXT TO COLUMNS
to split the field into bits (used the space between each name as the split)

Then i just took a few minutes of time to scan through the list results.
Most of the names had split fine, and for maybe 5% i just adjusted them to
look the way the should, it didn't take too long and it was a long list!

Then i imported everything back into access.

It was slightly fiddly, but not too much.

Let us know what you end up doing,

Kirstie

 

[Kirstie Adam]

sorry, just realised what newsgroup you were posting in, you were probably
not looking for something so basic!!

 

[Kelvin Beaton]

Thanks for the reply... I've tried the "slightly fiddly" route but am hoping
for a better option.

I'm hoping the people supplying the data will give the data to us in
seperate fields... it might happen... some day.

Thanks

Kelvin