How do I Break Out First and Last Name from a string?

I have a field which contains names (LastName, FirstName). How can I break First and Last Name out into seperate fields?
LVL 11
EMCITAsked:
Who is Participating?
 
Evan CutlerConnect With a Mentor Volunteer Chief Information OfficerCommented:
then go with Right(input, instr(input, ',') +2)...that should work.
I'm assuming always a comma then space.
0
 
pritamduttCommented:
Since you have not mentioned any specific application or Product, Here is an VBA Example:

Sub SplitDemo()
    Dim txt As String
    Dim x As Variant
    txt = "LastName, FirstName"
    x = Split(txt, ",")
    Debug.Print "LastName" & x(0)
    Debug.Print "FirstName" & x(1)

End Sub

Open in new window

0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
you can use substring functions or regular expressions, pending how you are programming....

What language?  and can you provide the background of what you are putting together?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
EMCITAuthor Commented:
My apologies. It is MS Access 2003
0
 
EMCITAuthor Commented:
I have a spreadsheet with two fields: Name (LastName, FirstName format) and Employee Number. I imported it into an Access DB table and I want to create two new fields in that table: FirstName and LastName and transform the string in the original field to popluate these two new fields.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...you use VB.

Dim fname as string
Dim lname as string
Dim input as string <---input is the string incoming...you can remove if you have it...I put it here for ref.

fname = Left(input , instr(input, ','))
lname = Right(input, instr(input, ',))

basically you are going to use a Left and Right function to get your values...
if you get a comma in one of them, adjust by one (for example Left(input, instr(input,',') -1 )
the function instr returns a number you can add/subtract.  by adding/subtracting you are moving the stop point...
Cheers
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Sorry...didn't see the order...
switch Left for Right and Right for left to ensure you have the right format.
0
 
pritamduttCommented:
Assuming you have field called Name..

Please run the following query

SELECT Left(Name,Instr(Name,",")-1) as lstname, Right(Name,InstrRev(Name,",")+1) as fname
FROM tblName;


Hope this helps!

Regards,
0
 
EMCITAuthor Commented:
fname = Left(input , instr(input, ',')-1) works fine to return the Last Name, without the comma. lname = Right(input, instr(input, ',))
is returning, for exampl - Smith, John is returning "h, John" The fields are all in the LastName, FirstName format.
0
 
pritamduttCommented:
Have you checked the query I have given you?
0
 
pritamduttCommented:
Sorry the updated query is

SELECT Left(Name,Instr(Name,",")-1) as lstname, Right(Name,InstrRev(Name,",")) as fname
FROM tblName

Open in new window



Regards,
0
 
EMCITAuthor Commented:
Right(Name,InstrRev(Name,",")+1) returns the following:
Name                      Last        First
Allsop, Chris      Allsop        p, Chris
Ataellahi, Alireza      Ataellahi        hi, Alireza
Atkinson, Amanada      Atkinson        n, Amanada
Awram, Nicole      Awram         Nicole
Barnes, Melissa      Barnes         Melissa
Belleghem, R      Belleghem  elleghem, R
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...now I can see it.  last one, I promise.  lol

Right(Name, INSTRREV(Name,",")-2)

by adding and subtracting from the return of the instrRev, you are moving the pointer to where you want it to start.

0
 
EMCITAuthor Commented:
Right(Name, INSTRREV(Name,",")-2) returns:

      Name      Last      First
      Atkinson, Amanada      Atkinson      Amanada
      Awram, Nicole      Awram      cole
      Barnes, Melissa      Barnes      lissa
      Belleghem, R      Belleghem      eghem, R
      Belley, Suzanne      Belley      zanne
0
 
pritamduttConnect With a Mentor Commented:
Hi I have tested the sample data provided by you using the following query

SELECT Left(Name,Instr(Name,",")-1) as lstname, Right(Name,Len(Name) - Instr(Name,",")) as fname
FROM tblName

and here are the results

lstname      fname
Allsop       Chris
Ataellahi       Alireza
Atkinson       Amanada
Awram       Nicole
Barnes       Melissa
Belleghem       R


Regards,
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
oh, I see what your doing....
ok, i fyou are going to use a INSTRREV, remove the -2
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
if that doesn't work, then add a space after the comma
INSTR(Name, ', ')
0
 
EMCITAuthor Commented:
Thank you Experts!
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.

All Courses

From novice to tech pro — start learning today.