Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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.
0
Brogrim
Asked:
Brogrim
  • 4
2 Solutions
 
mbizupCommented:
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 FeddemaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now