Spliting First Middle and Last Name

[lindag]

We have fields that are LastName/FirstName Middle and sometimes a
number, ex. Smith/Linda S 3. Any suggestions how to split up and
not include the number?

 

[Jerry Whittle]

If the last and first names are always separated by a /:

LastName: Left([FieldName],instr([FieldName],"/")-1)

There is an issue with the middle name if there is anyone like “Billy Bobâ€
or “Mary Annâ€. You may need to 'fix' them somehow before running the
following:

FirstName: Mid([FieldName],instr([FieldName],"/")+1, instr([FieldName],"
")-instr([FieldName],"/")-1)

If there isn’t a middle initial, Access will return an empty string. That
can be a problem sometimes when you are looking for null values. You’ll need
to search on nulls and “â€.

MI: Trim(Mid([FieldName],instr([FieldName]," "),3))

 

[John W. Vinson]

We have fields that are LastName/FirstName Middle and sometimes a
number, ex. Smith/Linda S 3. Any suggestions how to split up and
not include the number?

This can get complicated. Some people have two middle names (Tolkien/John R
R); some have none, or no known one (Jones/Debbie 4).

You can use an expression like

Left([fullname], InStr([fullname], "/") - 1)

to extract the surname, and

Mid([fullname], InStr([fullname], "/") + 1)

to extract the rest; you can then do similar tricks using " " as the search
string in InStr to parse out the first and middle names. The IsNumeric()
function will identify numbers. But I suspect with the variability you'll need
some combination of VBA code and USB (Using Someone's Brain) to reliably
extract this!

 

[John Spencer]

Not easy.

Last Name is simple
Left([FullName],Instr(1,[FullName],"/")-1)
Remainder of name is simple
MID([FullName],Instr(1,[FullName],"/")+1)

That will work well to give you the two parts. But a name like
Smith_Jones/Mary Anne T 4
will give you lots of headaches getting the last section to break up properly.
The individual's first name is Mary Anne (including the space).

If the number part is always one digit at the very end, you could trim that
off the remainder.
IIF(Right(Remainder,1) Like "[0-9]",Left(Remainder,Len(Remainder)-1),Remainder)

Then you could take that and look for space and split the remainder based on
that space.

It would probably be best to use a custom VBA expression to return the name
parts or some regular expressions to do so.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

m:

We have fields that are LastName/FirstName Middle and sometimes a
number, ex. Smith/Linda S 3. Any suggestions how to split up
and not include the number?

This doesn't address your specific data layout, but the code I
posted here might give you a starting point:

http://tinyurl.com/y9rfots =>
http://superuser.com/questions/108445/how-to-swap-first-and-last-name
s-in-ms-access

In that case, the data was in the format "Firstname Lastname" but if
you have a uniform format, or a finite number of variations in the
format, the code there could be altered to process your data.