Field - Formula Help

  • Thread starter Thread starter Lee-Anne Waters via AccessMonster.com
  • Start date Start date
L

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
 
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.
Click to expand...

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]
 
thanks John. you have saved me lots of time and i have gained a little
knowledge along the way
 
Back
Top