Solved

Seperate a Full Name into 1st and Second

Posted on 2011-09-07
5
209 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now