By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Need to parse Name field so I have to fields, FirstName, LastName

P: n/a
I have an Excel table I need to import into Access. The name is entered into one field "Name". I'd like to have two fields in Access, FirstName and LastName. How do I do this.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Look at the following functions in the Help file:
InStr
Left
Mid
Right
You will need to use these with an append or update query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Theresa Hancock via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:20******************************@AccessMonste r.com...
I have an Excel table I need to import into Access. The name is entered into one field "Name". I'd like to have two fields in Access, FirstName and
LastName. How do I do this.
--
Message posted via http://www.accessmonster.com

Nov 13 '05 #2

P: n/a
Theresa Hancock via AccessMonster.com wrote:
I have an Excel table I need to import into Access. The name is entered into one field "Name". I'd like to have two fields in Access, FirstName and LastName. How do I do this.


http://www.fotosearch.com/bigcomps/A...182/OBJ019.jpg

What Steve said, you can easily split up my name:

FirstName = Left(Name,Instr(1,Name," ")-1)
SurName = Mid(Name,Instr(1,Name," ")+1)

Then you run into the people you didn't expect, from this newsgroup,
"Tom van Stiphout" will give you a bit of trouble (not personally I'm
sure) but in his case the above code would be correct as "van" is part
of his surname but beware people with middle names and/or
(first)initials (Michael J Fox, C Thomas Howell).
Then there's the people with titles, Gurus, Monsignors, Doctors,
Reverends, Professors, Armed forces ranks, etc. People with letters
after their name, OBE, KBE, MITE, MVP :-)

There have been many threads on this, GIYF.

--
This sig left intentionally blank
Nov 13 '05 #3

P: n/a
The NEATCODE.MDB available at

http://support.microsoft.com/kb/148402/EN-US/

has name parsing code (and much more, you can learn a lot from the
example code) This may have a bit more than what you need, but these
will let you extract first and last name from a string. You could also
get Title, Degree or Pedigree by making functions similar to lastname
and firstname.

It's probably not horribly efficient, but I've used it for name
clean-up for years.

Function lastname(NS As String)
Dim Title As String
Dim FName As String
Dim MName As String
Dim LName As String
Dim Pedigree As String
Dim Degree As String

Call ParseName(NS, Title, FName, MName, LName, Pedigree, Degree)
lastname = LName
End Function

Function firstname(NS As String)
Dim Title As String
Dim FName As String
Dim MName As String
Dim LName As String
Dim Pedigree As String
Dim Degree As String

Call ParseName(NS, Title, FName, MName, LName, Pedigree, Degree)
firstname = FName
End Function

Function shortname(NS As String)
Dim Title As String
Dim FName As String
Dim MName As String
Dim LName As String
Dim Pedigree As String
Dim Degree As String
Call ParseName(NS, Title, FName, MName, LName, Pedigree, Degree)
shortname = Left(Left(FName, 1) & " " & LName & "
", 15)
End Function

Sub ParseName(ByVal s As String, Title As String, FName As String,
MName As String, LName As String, Pedigree As String, Degree As String)
'
' Parses name "Mr. Bill A. Jones III, PhD" into separate fields.
' Words are extracted in the following order: Title, Degree, Pedigree,
LName, FName, MName
' Assumes Pedigree is not preceded by a comma, or else it will end up
with the Degree(s).
'
Dim Word As String, P As Integer, Found As Integer
Const Titles =
"Mr.Mrs.Ms.Dr.Mme.Mssr.Mister,Miss,Doctor,Sir,Lord ,Lady,Madam,Mayor,President"
Const Pedigrees = "Jr.Sr.III,IV,VIII,IX,XIII"
Title = ""
FName = ""
MName = ""
LName = ""
Pedigree = ""
Degree = ""
'
' Get Title
'
Word = CutWord(s, s)
If InStr(Titles, Word) Then
Title = Word
Else
s = Word & " " & s
End If
'
' Get Degree
'
P = InStr(s, ",")
If P > 0 Then
Degree = Trim$(Mid$(s, P + 1))
s = Trim$(Left$(s, P - 1))
End If
'
' Get Pedigree
'
Word = CutLastWord(s, s)
If InStr(Pedigrees, Word) Then
Pedigree = Word
Else
s = s & " " & Word
End If
'
' Get Last Name
'
LName = CutLastWord(s, s)
'
' Get First Name
'
FName = CutWord(s, s)
'
' Get Middle Name(s)
'
MName = Trim(s)
End Sub
Sub TestParseName()
Dim N As String, t As String, f As String, M As String, L As String, P
As String, D As String
N = "Dr. James George William Joyce-Brothers IV, MS, PhD"
ParseName N, t, f, M, L, P, D
Debug.Print t, f, M, L, P, D
N = "New York NY 45678-9876"
ParseCSZ N, t, f, M
Debug.Print t, f, M
End Sub

Function CountCSVWords(s) As Integer
'
' Counts words in a string separated by commas
'
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSVWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, ",")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, ",")
Loop
CountCSVWords = WC
End Function

Function CountSWords(s, delimiter As String) As Integer
'
' Counts words in a string separated by commas
'
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, delimiter)
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, delimiter)
Loop
CountSWords = WC
End Function

Function CountWords(s) As Integer
'
' Counts words in a string separated by 1 or more spaces
'
Dim WC As Integer, i As Integer, OnASpace As Integer
If VarType(s) <> 8 Or Len(Trim(s)) = 0 Then
CountWords = 0
Exit Function
End If
WC = 0
OnASpace = True
For i = 1 To Len(s)
If Mid(s, i, 1) = " " Then
OnASpace = True
Else
If OnASpace Then
OnASpace = False
WC = WC + 1
End If
End If
Next i
CountWords = WC
End Function

Function CutFirstWord(s, Remainder)
'
' CutWord: returns the first word in S.
' Remainder: returns the rest.
'
' Words are delimited by spaces
'
Dim temp, i As Integer, P As Integer
temp = Trim(s)
P = InStr(temp, " ")
If P = 0 Then
CutFirstWord = temp
Remainder = Null
Else
CutFirstWord = Left(temp, P - 1)
Remainder = Trim(Mid(temp, P + 1))
End If
End Function

Function CutLastWord(s, Remainder)
'
' CutWord: returns the first word in S.
' Remainder: returns the rest.
'
' Words are delimited by spaces
'
Dim temp, i As Integer, P As Integer
temp = Trim(s)
P = 1
For i = Len(temp) To 1 Step -1
If Mid(temp, i, 1) = " " Then
P = i + 1
Exit For
End If
Next i
If P = 1 Then
CutLastWord = temp
Remainder = Null
Else
CutLastWord = Mid(temp, P)
Remainder = Trim(Left(temp, P - 1))
End If
End Function

Function CutWord(s, Remainder)
'
' CutWord: returns the first word in S.
' Remainder: returns the rest.
'
Dim temp, P As Integer
temp = Trim(s)
P = InStr(temp, " ")
If P = 0 Then P = Len(temp) + 1
CutWord = Left(temp, P - 1)
Remainder = Trim(Mid(temp, P + 1))
End Function

Function GetCSVWord(s, Indx As Integer)
Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
WC = CountCSVWords(s)
If Indx < 1 Or Indx > WC Then
GetCSVWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, ",") + 1
Next Count
EPos = InStr(SPos, s, ",") - 1
If EPos <= 0 Then EPos = Len(s)
GetCSVWord = Mid(s, SPos, EPos - SPos + 1)
End Function

Function GetSWord(s, delimiter As String, Indx As Integer)
Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
WC = CountSWords(s, delimiter)
Select Case Indx
Case Is = 0
GetSWord = s
Exit Function
Case Is < 1
GetSWord = Null
Exit Function
Case Is > WC
GetSWord = Null
Exit Function
End Select
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, delimiter) + Len(delimiter)
Next Count
EPos = InStr(SPos, s, delimiter) - Len(delimiter)
If EPos <= 0 Then EPos = Len(s)
GetSWord = Trim(Mid(s, SPos, EPos - SPos + Len(delimiter)))
End Function

Function GetWord(s, Indx As Integer)
'
' Extracts a word in text where words are separated by 1 or more spaces
'
Dim i As Integer, WC As Integer, Count As Integer, SPos As Integer,
EPos As Integer, OnASpace As Integer
WC = CountWords(s)
If Indx < 1 Or Indx > WC Then
GetWord = Null
Exit Function
End If
Count = 0
OnASpace = True
For i = 1 To Len(s)
If Mid(s, i, 1) = " " Then
OnASpace = True
Else
If OnASpace Then
OnASpace = False
Count = Count + 1
If Count = Indx Then
SPos = i
Exit For
End If
End If
End If
Next i
EPos = InStr(SPos, s, " ") - 1
If EPos <= 0 Then EPos = Len(s)
GetWord = Mid(s, SPos, EPos - SPos + 1)
End Function

Nov 13 '05 #4

P: n/a
On Fri, 28 Jan 2005 20:07:36 GMT, "Theresa Hancock via
AccessMonster.com" <fo***@AccessMonster.com> wrote:
I have an Excel table I need to import into Access. The name is entered into one field "Name". I'd like to have two fields in Access, FirstName and LastName. How do I do this.


If you have "Outlook" loaded on your machine, the following code is
from a from that tests references to "Outlook's" name parsing routines
in it's "ContactItem" object. It's pretty good (but very slow) as long
as your data is in one of the following formats:

1) "Sorted Order"
Warren, Mr. Tom W. Jr
' for "sorted order" a comma must diliminate the last name. Then the
rest must be in the following order Title, First, Middle, Suffix.
Although the Title, Middle, and Suffix are optional.

1) "Printed Order"
Mr. Tom W. Warren Jr
' for "printed order" full name must be in the following order
Title, First, Middle, Last, Suffix. Although the Title, Middle, and
Suffix are optional.

My recollection of "ParseName" (referenced elsewhere in this thead)
from NEATCODE.MDB is that it only parses names in "Printed Order".

Tom
' Be sure to set reference. Tools>References...>Microsoft Outlook X.X
Object library
' Use "Ojbect Browser" on "Outlook" library and "ContactItem" to
browse for other desired properties.

Private Sub RunButton_Click()
Dim DB As Database ' Current database
object
Dim rs As Recordset ' Names record set
object
Dim R As Long ' current record
Dim T As Long ' time
Dim olApp As Outlook.Application ' outlook object
Dim olCi As Outlook.ContactItem ' contact object

Set olApp = New Outlook.Application ' create outlook
reference
Set olCi = olApp.CreateItem(olContactItem) ' create contact

Set DB = CurrentDb ' create reference to
current db
Set rs = DB.OpenRecordset("tNames") ' create reference to
open table
T = Timer ' get starting time
R = 1 ' initialize record
count

rs.MoveFirst ' initialize record set
Do Until rs.EOF ' loop through names
rs.Edit ' edit address record
olCi.FullName = rs!FullName ' parse full name

rs!Title = olCi.Title ' load Title
rs!First = olCi.FirstName ' load First
rs!Middle = olCi.MiddleName ' load Middle
rs!Last = olCi.LastName ' load Last
rs!Degree = olCi.Suffix ' load Degree
rs!Gender = olCi.Gender ' load Gender
olCi.Close olDiscard ' close client record,
discard changes
R = R + 1 ' increament record
count
If R Mod 1000 = 0 Then Me.CurRec = R: Me.Repaint ' display record
count
rs.Update ' save record changes
rs.MoveNext ' goto next record
Loop ' next address
MsgBox "Time = " & Timer() - T ' display total time
olApp.Quit ' quit outlook
Set olCi = Nothing ' free ContactItem
resource
Set olApp = Nothing ' free Outlook
application resource
End Sub
Nov 13 '05 #5

P: n/a
Per Theresa Hancock via AccessMonster.com:
I have an Excel table I need to import into Access. The name is entered into one field "Name". I'd like to have two fields in Access, FirstName and LastName. How do I do this.


One time deal, right?

What kind of volume? If it's only a few hundred, you can do a quick-n-dirty
followed up by manual adjustments.

If volume is high, look before you leap. The O's and Mc's and Mac's and Di's
and Del's and Della's will drive you nuts - not to mention the single-letter
surnames....
--
PeteCresswell
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.