• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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?
0
EMCIT
Asked:
EMCIT
  • 7
  • 6
  • 5
2 Solutions
 
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 CutlerCommented:
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
 
EMCITAuthor Commented:
My apologies. It is MS Access 2003
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 CutlerCommented:
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 CutlerCommented:
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
 
Evan CutlerCommented:
then go with Right(input, instr(input, ',') +2)...that should work.
I'm assuming always a comma then space.
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 CutlerCommented:
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
 
pritamduttCommented:
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 CutlerCommented:
oh, I see what your doing....
ok, i fyou are going to use a INSTRREV, remove the -2
0
 
Evan CutlerCommented:
if that doesn't work, then add a space after the comma
INSTR(Name, ', ')
0
 
EMCITAuthor Commented:
Thank you Experts!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 7
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now