Solved

Seperate a Full Name into 1st and Second

Posted on 2011-09-07
5
212 Views
Last Modified: 2012-08-13
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
Comment
Question by:Brogrim
  • 4
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 36496644
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
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 36496946
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36496955
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36496966
Take a look at my sample database on splitting and concatenating name and address data:
http://www.helenfeddema.com/Files/code37.zip
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36496970
And if you have some Hispanic or Arabic names in the table, all bets are off!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question