?
Solved

Seperate a Full Name into 1st and Second

Posted on 2011-09-07
5
Medium Priority
?
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 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 1000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

718 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