Field - Formula Help

[Lee-Anne Waters via AccessMonster.com]

Hi,

i have just inherited a database that has over 1000 records and in the main
employee table there is a field call Contact Name. this field contains the
full name. ie Bill Smith

what i need to do is to be able to split this field into first name and
last name. preferably without having to do it manually.

is there a formula that i can use that will take this field and split the
data into two extra fields. one called First_Name and Last_Name.

any help much appreciated
thanks
Lee-Anne

 

[John Vinson]

Hi,

i have just inherited a database that has over 1000 records and in the main
employee table there is a field call Contact Name. this field contains the
full name. ie Bill Smith

what i need to do is to be able to split this field into first name and
last name. preferably without having to do it manually.

is there a formula that i can use that will take this field and split the
data into two extra fields. one called First_Name and Last_Name.

A *partial* solution is to update First_Name to

Left([Contact Name], InStr([Contact Name], " ") - 1)

and LastName to

Mid([Contact Name], InStr([Contact Name], " ") + 1)

InStr finds the position of the first blank in the name, and Left()
and Mid() return the substrings up to that point or from that point
on.

I say partial because names like "Billy Bob Thornton" and "Rhoda Mae
Johnson" will not be parsed correctly. You will want to run a query
searching with a criterion

LIKE "* *"

on Last_Name after you run this first query to find the exceptions.


John W. Vinson[MVP]

 

[Lee-Anne Waters via AccessMonster.com]

thanks John. you have saved me lots of time and i have gained a little
knowledge along the way