Seperate a Full Name into 1st and Second

I have received a table with a Name column which contains the 1st and 2nd name

How can I seperate them into 2 coulums

1st Name
2nd Name

I assume its soem sort of left and right function.
BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Assuming Access database, and that names are formatted as "Bob Smith":

Update YourTable
SET First = LEFT(YourField, Instr(1,YourField," ")-1), LAST = mid(YourField, instr(1, YourField, " ")+ 1)

This simply handles names as "Firstname LastName".  It will not handle names like "Mary Anne Jones"
0
 
Helen FeddemaConnect With a Mentor Commented:
The above expressions will work if (and only if) the full name field contains just the first name (a single word), a space, and the last name (a single word).  Since this is unlikely in the real world, you should expect to do some manual cleanup after updating the fields.
0
 
Helen FeddemaCommented:
Actually, I think the last name could be multi-word - but not the first name.  Otherwise the expressions will fail on first names like Mary Jane.
0
 
Helen FeddemaCommented:
Take a look at my sample database on splitting and concatenating name and address data:
http://www.helenfeddema.com/Files/code37.zip
0
 
Helen FeddemaCommented:
And if you have some Hispanic or Arabic names in the table, all bets are off!
0
All Courses

From novice to tech pro — start learning today.