importing table from excel need to split name field

[ISTodd]

Im importing a table from excel and I have a full name field set-up (for the most part)

Smith, John

there are occasional middle names

Smith, John Edward

Also there is the occasional

Smith, J. Edward

Any ideas on how to split this to 3 fields

last, first, Middle

Would it be easier to do it in Excel?

Thanks for any help.... Todd

 

[jebry]

Hi Todd!

Assuming your name fields are consistant in the use of commas and spaces then you can use the following procedures:

Public Function ReturnLastName(FullName As Variant) As String

Dim strFullName As String
Dim lngCommaPos As Long
Dim lngSpacePos As Long

If IsNull(FullName) = True Then
ReturnLastName = ""
Else
strFullName = FullName
lngCommaPos = InStr(strFullName, ",")
If lngCommaPos = 0 Then
ReturnLastName = strFullName 'ie only a last name
Else
returnLastName = Left(strFullName, lngCommaPos - 1)
End If
End If

End Function

Public Function ReturnFirstName(FullName As Variant) As String

Dim strFullName As String
Dim lngCommaPos As Long
Dim lngSpacePos As Long

If IsNull(FullName) = True Then
ReturnFirstName = ""
Else
strFullName = FullName
lngCommaPos = InStr(strFullName, ",")
If lngCommaPos = 0 Then
ReturnFirstName = ""
Else
lngSpacePos = InStr(lngCommaPos + 2, strFullName, " ")
If lngSpacePos = 0 Then
ReturnFirstName = Mid(strFullName, lngCommaPos + 2)
Else
ReturnFirstName = Mid(strFullName, lngCommaPos + 2, lngSpacePos - lngCommaPos - 2)
End If
End If
End If

End Function

Public Function ReturnMiddleName(FullName As Variant) As String

Dim strFullName As String
Dim lngCommaPos As Long
Dim lngSpacePos As Long

If IsNull(FullName) = True Then
ReturnMiddleName = ""
Else
strFullName = FullName
lngCommaPos = InStr(strFullName, ",")
If lngCommaPos = 0 Then
ReturnMiddleName = ""
Else
lngSpacePos = Instr(lngCommaPos + 2, strFullName, " ")
If lngSpacePos = 0 Then
ReturnMiddleName = ""
Else
ReturnMiddleName = Mid(strFullName, lngSpacePos + 1)
End If
End If
End If

End Function

hth


Jeff Bridgham
bridgham@purdue.edu

 

[TonyJollans]

Hi ISTodd,

Try this ..

[blue]LastName = Split(FullName, ",")(0)
FirstName = Split(Trim(Split(FullName, ",")(1)))(0)
MiddleName = Split(Trim(Split(FullName, ",")(1)))(1)[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ISTodd]

Thanks for responding to my inquiry. Since I am really just learning Access, could you offer a little more basic assistance to running the code. For example:

Do I need to add anything (really basic or elementary) to this?

Do I just open a new module and paste what was written? - Then what?

I really do appreciate any help--

Thanks!
Todd